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

[Anonymous]

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

[swuehl]

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]

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 · Accepted solution]

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)

)