Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count w/ Aggr

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.

qv example.JPG

See my attached example file. 

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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)

View solution in original post

4 Replies
maxgro
MVP
MVP

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
MVP
MVP

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)

Not applicable
Author

Thank you!!!