Aggr Issue

[#1]

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

[jonathandienst · Accepted solution]

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

[#3]

Nice One Jonathan.

That looks like it did the trick just nicely

Many thanks

Les