Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am trying to get a visualisation that shows the number (count) of markets that exceed 100% for each of our financial measures. So, i have been looking into the Aggr function.
I have this so far aggr(1-(sum(CERPSGLVal) - sum(PL_ITEM_AMT))/sum(CERPSGLVal),Mkt_Cd,Rep_Description) which gives me the same data as my normal calculation 1-(sum(CERPSGLVal) - sum(PL_ITEM_AMT))/sum(CERPSGLVal).
Now I need to work out how to return the number of markets that exceed 100%. I have tried the below, but I get no data.
sum(if(aggr(1-(sum(CERPSGLVal) - sum(PL_ITEM_AMT))/sum(CERPSGLVal),Mkt_Cd,Rep_Description)>1,1,0)
Any help would be appreciated.
Regards
Les
Hi
I think this is what you need:
sum(aggr(If((1-(sum(CERPSGLVal) - sum(PL_ITEM_AMT)) / sum(CERPSGLVal)) > 1, 1, 0), Mkt_Cd, Rep_Description))
HTH
Jonathan
Hi
I think this is what you need:
sum(aggr(If((1-(sum(CERPSGLVal) - sum(PL_ITEM_AMT)) / sum(CERPSGLVal)) > 1, 1, 0), Mkt_Cd, Rep_Description))
HTH
Jonathan
Nice One Jonathan.
That looks like it did the trick just nicely
Many thanks
Les