how to find variance in script

[shruthibk]

hi all,

i have fields like,metric

                         submetric,

                          report date,

                          monthname(report date) as report month

                            counter

where i need to find 

1) sum(counter)-sum(Previous month counter) and

2) sum(Counter)/sum(Previous month counter)-1   in pivot table  

i am using left join to find the previous month counter like this

Tab1:     

load                  metric

                         submetric,

                          report date,

                          monthname(report date) as report month

                 monthname(addmonths(report date)-1) as prev report month

                            counter

left join

report month as prev report month

counter as previouscounter

resident Tab1

it is giving cartesian join result can anyone give me suggestions to resolve this

thanks

shruthi

[Digvijay_Singh]

I think you can do this in front end without changes in script, by taking month as dimension and Above() function in expression to calculate previous month counter.

[MayilVahanan]

Hi

Try like this

Tab1:    

load                  metric

                         submetric,

                          report date,

                          monthname(report date) as report month

                 monthname(addmonths(report date)-1) as prev report month

                            counter

left join

Load

metric,

  submetric,

report month as prev report month

counter as previouscounter

resident Tab1

[shruthibk]

Hi i tried in UI  sum(counter)-sum(Previous month counter)  this i am able to achieve in UI using above function but  when i am trying for next expression it is throwing internal fatal error

[shruthibk]

hi i am getting null values in the previous counter when i go for this approach

[shruthibk]

MOM % change 2) sum(Counter)/sum(Previous month counter)-1 this one i am not able to achieve in UI