Count w/ Aggr

[#1]

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. 

[maxgro]

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)

[sunny_talwar]

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'

[swuehl · Accepted solution]

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)

[#5]

Thank you!!!