3 Replies Latest reply: Jan 22, 2015 4:42 AM by Gysbert Wassenaar RSS

    Script

      Hi

       

      the below script shows zero result, could you please advise

       

      =

      sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}>} 

       

       

      sum(if([PRODUCTION LOCATION]=[SALES LOCATION],

      ([PRODUCTION FOB QTY]+([PRODUCTION SUB QTY]*(-1)))*([FOB WITHOUT COMMISSION]/[PACK QTY]),

      0)

       

       

      +

      sum([PRODUCTION SUB QTY]*[SUB CM])))

        • Re: Script
          Gysbert Wassenaar

          If you nest aggregation functions like sum then you need to use the aggr function to specify over which dimensions the inner sum should be aggregated.

           

          =sum({$<YEAR = {$(=max(YEAR))},MONTH = {"<=$(=max({<YEAR={$(=max(YEAR))}>} MONTH))"}>}

          aggr(sum(if([PRODUCTION LOCATION]=[SALES LOCATION],

          ([PRODUCTION FOB QTY]+([PRODUCTION SUB QTY]*(-1)))*([FOB WITHOUT COMMISSION]/[PACK QTY]),0)

          + aggr(sum([PRODUCTION SUB QTY]*[SUB CM]),DimP,DimQ,...,DimY),DimA,DimB,...,DimX))