topic Re: Count w/ Aggr in QlikView

Count w/ Aggr

Tue, 23 Feb 2016 21:48:18 GMT

Hi All,

I have been away from QlikView for a while, so I apologize in advance for my rusty QV skills. I have a chart that shows a variance from month to month and denotes whether this variance represents an Increase, a Decrease, a New Code, or a Lost Code. This works as desired. However, I want to get a separate chart that shows the breakdown of the items. I used an Aggr() formula to get the # of increases and the # of decreases, but can't seem to get the # New and # Lost to pull.

See my attached example file.

Re: Count w/ Aggr

maxgro — Tue, 23 Feb 2016 21:54:12 GMT

maybe a chart with

a calculated dimension

=aggr(

if(Sum({$<MonthYr={'Jan-15'}>} Amount)=0 and Sum({$<MonthYr={'Jan-16'}>} Amount)>0,'New',

if(Sum({$<MonthYr={'Jan-15'}>} Amount)>0 and Sum({$<MonthYr={'Jan-16'}>} Amount)=0,'Lost',

if(Sum({$<MonthYr={'Jan-15'}>} Amount)>Sum({$<MonthYr={'Jan-16'}>} Amount),'Decrease',

if(Sum({$<MonthYr={'Jan-15'}>} Amount)<Sum({$<MonthYr={'Jan-16'}>} Amount),'Increase')))),

Code)

an expression

count(DISTINCT Code)

Re: Count w/ Aggr

sunny_talwar — Tue, 23 Feb 2016 21:55:20 GMT

May be this:

Count(if(Aggr(Sum({$<MonthYr={'Jan-15'}>} Amount),Code)<Aggr(Sum({$<MonthYr={'Jan-16'}>} Amount),Code),1)) & ' Increases' & Chr(13) &

Count(if(Aggr(Sum({$<MonthYr={'Jan-15'}>} Amount),Code)>Aggr(Sum({$<MonthYr={'Jan-16'}>} Amount),Code),1)) & ' Decreases' & Chr(13) &

Count(Aggr(if(Sum({$<MonthYr={'Jan-15'}>} Amount)=0 and Sum({$<MonthYr={'Jan-16'}>} Amount)>0, 1), Code)) & ' New' & Chr(13) &

Count(Aggr(if(Sum({$<MonthYr={'Jan-15'}>} Amount)>0 and Sum({$<MonthYr={'Jan-16'}>} Amount)=0, 1), Code)) & ' Lost'

Re: Count w/ Aggr

swuehl — Tue, 23 Feb 2016 21:57:50 GMT

Maybe like this:

Sum(Aggr(If(Sum({$<MonthYr={'Jan-15'}>} Amount)=0 and Sum({$<MonthYr={'Jan-16'}>} Amount)>0,1),Code)) & ' New' & Chr(13) &

Sum(Aggr(If(Sum({$<MonthYr={'Jan-15'}>} Amount)>0 and Sum({$<MonthYr={'Jan-16'}>} Amount)=0,1),Code)) & ' Lost' & Chr(13)

Re: Count w/ Aggr

Tue, 23 Feb 2016 21:59:58 GMT

Thank you!!!