Aggr Issue

Wed, 15 Oct 2014 13:17:52 GMT

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

Re: Aggr Issue

jonathandienst — Wed, 15 Oct 2014 13:23:54 GMT

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

Re: Aggr Issue

Wed, 15 Oct 2014 13:35:27 GMT

Nice One Jonathan.

That looks like it did the trick just nicely

Many thanks

Les

topic Aggr Issue in QlikView

Aggr Issue

Re: Aggr Issue

Re: Aggr Issue