Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
)
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)
)
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.
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)
)