2 Replies Latest reply: Mar 15, 2012 8:30 PM by Michael Fenton RSS

    Aggregate chart expression problem

      Hi,

      I am having trouble with aggregation of chart expressions. 

      My client wants a table that displays Stock Turns by StockItem.  The calculation for this is CostOfSales/CostOnHand.  It is normally a value between 0 and 1 (eg 0.12).

      The data is stored in a StockPeriod table, which gives us those two values for every StockItem, Location and Month.

      For example:

      StockPeriod:
      LOAD * Inline [
      MonthStart,StockItem,Location,CostOfSales,CostOnHand
      1/01/2012,Product1,Location1,10,100
      1/01/2012,Product1,Location2,12,100
      1/01/2012,Product1,Location3,8,120
      1/01/2012,Product2,Location1,30,335
      1/01/2012,Product2,Location2,61,296
      1/01/2012,Product2,Location3,70,301
      1/01/2012,Product3,Location1,82,717
      1/01/2012,Product3,Location2,22,828
      1/01/2012,Product3,Location3,36,513
      1/02/2012,Product1,Location1,87,509
      1/02/2012,Product1,Location2,26,881
      1/02/2012,Product1,Location3,39,895
      1/02/2012,Product2,Location1,88,44
      1/02/2012,Product2,Location2,12,691
      1/02/2012,Product2,Location3,75,991
      1/02/2012,Product3,Location1,84,420
      1/02/2012,Product3,Location2,97,709
      1/02/2012,Product3,Location3,46,465
      1/03/2012,Product1,Location1,80,556
      1/03/2012,Product1,Location2,68,794
      1/03/2012,Product1,Location3,72,536
      1/03/2012,Product2,Location1,43,553
      1/03/2012,Product2,Location2,4,874
      1/03/2012,Product2,Location3,38,93
      1/03/2012,Product3,Location1,86,558
      1/03/2012,Product3,Location2,80,441
      1/03/2012,Product3,Location3,63,990];
      
      

       

      When calculating the Stock Turns for a StockItem, the values should be totalled across all Locations, the ratio calculated, then added together for each month.

      Given the data above, I can calculate the correct results using a hard-coded Set Analysis expression as follows:

      Sum({$<MonthStart={'1/01/2012'}>} CostOfSales) / Sum({$<MonthStart={'1/01/2012'}>} CostOnHand)
      +
      Sum({$<MonthStart={'1/02/2012'}>} CostOfSales) / Sum({$<MonthStart={'1/02/2012'}>} CostOnHand)
      +
      Sum({$<MonthStart={'1/03/2012'}>} CostOfSales) / Sum({$<MonthStart={'1/03/2012'}>} CostOnHand)
      
      

      Stock Turns.jpg

       

      Obviously this will not work for production, but I cannot develop the correct Aggr expression to make it work dynamically.

      Any help is very welcome.  I will attach my sample app.

        • Re: Aggregate chart expression problem
          jagan mohan rao appala

          Hi,

           

          Check the attachment for solution, hope it helps you.

           

          The expression used is

           

          =Sum(Aggr(Sum(CostOfSales)/Sum(CostOnHand), MonthStart, StockItem))

           

          Regards,

          Jagan.

            • Re: Aggregate chart expression problem

              Thanks for your help Jagan.

               

              That does work for the table I showed in my original post.

               

              It does not work, however, if you add the Location as a dimension.  I was trying to implement Location as a conditional dimension and allow the client to turn it on and off with a button.  Instead of this, I have created two seperate tables with different expressions and put them in a Container.  Also added a third table to the container to show them the breakdown by Month.

               

              Overall, I have given the client more information than they requested, but hopefully they can use it to validate the calculations and use it with confidence.

               

              Regards,

              Michael