2 Replies Latest reply: Oct 15, 2014 9:35 AM by Les Lockett RSS

    Aggr Issue

      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