Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

jenstarr
New Contributor III

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

Tags (3)
1 Solution

Accepted Solutions
jenstarr
New Contributor III

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

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)

)

3 Replies
MVP
MVP

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

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)

)

jenstarr
New Contributor III

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

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.  

jenstarr
New Contributor III

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

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)

)