6 Replies Latest reply: Jul 21, 2016 6:53 AM by Lasse Liltorp RSS

    How to sum/aggregate correctly an expression between actual period and before

    Lasse Liltorp

      For a sum of actual period the following expressions works fine on but item and aggregated category levels:

       

      (

      SUM(  {$< Item_Number = P({<FLAG_SoldPrevYear={">0"}>} Item_Number) >}  Spend_landed_DKK_excl_VAT) 

      /

      SUM( {$< Item_Number = P({<FLAG_SoldPrevYear={">0"}>} Item_Number) >} Actual_Quantity_Invoiced) 

      )

      *

      SUM( {$< Item_Number = P({<FLAG_SoldPrevYear={">0"}>} Item_Number) >} Actual_Quantity_Invoiced) 

       

      Example;

       

      For a sum of a calculation between before and actual period the sum on item level is correct but on category level it is not the "sum" of the items:

       

      (

      (

      Before(

      SUM(

      {$<

      Item_Number = P({<FLAG_SoldFollowingYear={">0"}>} Item_Number)

      >}

      Spend_landed_DKK_excl_VAT)

      /

      SUM(

      {$<

      Item_Number = P({<FLAG_SoldFollowingYear={">0"}>} Item_Number)

      >}

      Actual_Quantity_Invoiced)

      )

      )

      *

      SUM(

      {$<

      Item_Number = P({<FLAG_SoldPrevYear={">0"}>} Item_Number)

      >}

      Actual_Quantity_Invoiced)

      )




      Example:

      Udklip.PNG

       

      Sum Actual to higher level is correct.

       

      Sum Prev to higher level is incorrect; should have been 18.560.770

       

      Any experts who can see my error, maybe I should use aggr somehow?

       

      Best regards,

      LL