topic Re: how to find variance in script in QlikView

how to find variance in script

shruthibk — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: how to find variance in script

Digvijay_Singh — Mon, 07 Mar 2016 10:49:50 GMT

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.

Re: how to find variance in script

MayilVahanan — Mon, 07 Mar 2016 11:04:23 GMT

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

Re: how to find variance in script

shruthibk — Mon, 07 Mar 2016 11:15:48 GMT

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

Re: how to find variance in script

shruthibk — Mon, 07 Mar 2016 11:18:13 GMT

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

Re: how to find variance in script

shruthibk — Mon, 07 Mar 2016 11:19:23 GMT

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