Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

AGGR is only working when selection of Market and State fields are selected

This formula works only if I select a Market and a State, however if I look at all States within a Market, then this column is blank. 

sum(if(aggr(rank((sum({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >} RB_Amount)*1.03)*.2),Market,State,ACC_ID)

>aggr(count({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"}>}Total distinct ACC_ID),Market, State, ACC_ID)/2,

aggr(((sum({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"}>} RB_Amount)*1.03)*.2),Market, State, ACC_ID)))

Any help would be greatly appreciated. 

Thanks,

Jen

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Thank you for your help swuehl.  I was able to make a change to your original suggestions to make it work.  The change I made is in Bold.

Thank you again!

Sum(

aggr(

if( rank((sum({<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >}  RB_Amount)*1.03)*.2)

> count({<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >} Total  <Market,State> ACC_ID) /2,

((sum({<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >} RB_Amount)*1.03)*.2))

,Market,State,ACC_ID)

)

View solution in original post

3 Replies
swuehl
MVP
MVP

What if you use only 1 aggr() function:

sum(

aggr(

if( rank((sum({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >} RB_Amount)*1.03)*.2)

> count({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"}>}Total distinct ACC_ID) /2,

((sum({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"}>} RB_Amount)*1.03)*.2)

,Market,State,ACC_ID)

)

Anonymous
Not applicable
Author

Thank you for your reply! 

If I only use one aggr() then the >count({$<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"}>}Total distinct ACC_ID) /2, doesn't work properly.  What I'm trying to accomplish is this:

For projections, half of all accounts need to be upgraded by market and by state. I need to show two columns one with the minimum upgrade dollars and one with the maximum upgrade dollars.  I did this by ranking the accounts, then if the rank is greater then half the number of accounts, by market by state, then I do the upgrade calculation for the minimum upgrade dollars.  I do the same for the maximum upgrade dollars, but the rank is less than or equal to half the accounts.  

Anonymous
Not applicable
Author

Thank you for your help swuehl.  I was able to make a change to your original suggestions to make it work.  The change I made is in Bold.

Thank you again!

Sum(

aggr(

if( rank((sum({<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >}  RB_Amount)*1.03)*.2)

> count({<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >} Total  <Market,State> ACC_ID) /2,

((sum({<Sub_Start_Date={">=$(=vMinDate)<=$(=vMaxDate)"} >} RB_Amount)*1.03)*.2))

,Market,State,ACC_ID)

)