3 Replies Latest reply: Dec 2, 2013 11:15 AM by Jen Starr

# 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

• ###### 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)

)

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

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.

• ###### 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)

)