0 Replies Latest reply: Feb 3, 2016 8:32 AM by Johannes Mathis RSS

    Set Analysis for Previous Year

    Johannes Mathis

      Hi all,

       

      I have a question related set anaylsis for previous year.

      I use a long formula with some if-ytatements to calculate a certain KPI. (see formula below)

       

      Now I want to do exactly the same for previous year.

      In my dataset it is only possible to select one year and one month.

      Therefore I thought that I could behindeach sum(/only( this set Analysis: {$<Year = {$(=Only(Year)-1)}>} 

       

      But this do not work and I do not know why...

       

      Your Feedback is appreciated!

       

      Thanks a lot in advance!

       

      KR

      Joe

       

      Original formula:

      sum(
      aggr(
      (
      if(
      round(sum(Quantity_IO)) = 0 and round(sum(GoodsRec_Quantity)) = 0
      ,(
      sum(AMOUNT_SOC_IO_GRIR_EURX_SOC) + sum(AMOUNT_SOC_IO_PCV_EURX_SOC) + sum(AMOUNT_SOC_AC_PCV_EURX_LSC) + sum(AMOUNT_SOC_AC_FCC_EURX_LSC) )
      ,
      if(
      round(sum(GoodsRec_Quantity)) = 0 and round(sum(Quantity_IO)) <> 0
      ,0
      ,
      if(
      round(sum(GoodsRec_Quantity)) <> 0 and round(sum(Quantity_IO)) = 0 //<= statt =
      , (sum(AMOUNT_SOC_GM_GRIR_EURX_SOC) + sum(AMOUNT_SOC_GM_FCC_EURX_SOC) + sum(AMOUNT_SOC_AC_PCV_EURX_LSC))
      ,
      if(
      round(sum(GoodsRec_Quantity)) <> round(sum(Quantity_IO))
      ,
      if(
      (
      sum(AMOUNT_SOC_IO_GRIR_EURX_SOC) + sum(AMOUNT_SOC_IO_PCV_EURX_SOC) + sum(AMOUNT_SOC_AC_PCV_EURX_LSC) + sum(AMOUNT_SOC_AC_FCC_EURX_LSC) ) = 0
      ,
      sum(GoodsRec_Quantity_orig)
      , (
      sum(GoodsRec_Quantity_orig) - sum(Quantity_IO_orig))
      )
      *
      only(
      STD_PRICE_LSC)
      +
      (
      sum(AMOUNT_SOC_IO_GRIR_EURX_SOC) + sum(AMOUNT_SOC_IO_PCV_EURX_SOC) + sum(AMOUNT_SOC_AC_PCV_EURX_LSC) + sum(AMOUNT_SOC_AC_FCC_EURX_LSC) ) 
      ,(
      sum(AMOUNT_SOC_IO_GRIR_EURX_SOC) + sum(AMOUNT_SOC_IO_PCV_EURX_SOC) + sum(AMOUNT_SOC_AC_PCV_EURX_LSC) + sum(AMOUNT_SOC_AC_FCC_EURX_LSC) )
      )
      )
      )
      )
      )
      ,
      REF_KEY, Material)
      )

       

      Formula with set Analysis (do not work...):

      sum(
      aggr(
      (
      if(
      round(sum( {$<Year = {$(=Only(Year)-1)}>} Quantity_IO)) = 0 and round(sum( {$<Year = {$(=Only(Year)-1)}>} GoodsRec_Quantity)) = 0
      ,(
      sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_IO_GRIR_EURX_SOC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_IO_PCV_EURX_SOC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_AC_PCV_EURX_LSC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_AC_FCC_EURX_LSC) )
      ,
      if(
      round(sum( {$<Year = {$(=Only(Year)-1)}>} GoodsRec_Quantity)) = 0 and round(sum( {$<Year = {$(=Only(Year)-1)}>} Quantity_IO)) <> 0
      ,0
      ,
      if(
      round(sum( {$<Year = {$(=Only(Year)-1)}>} GoodsRec_Quantity)) <> 0 and round(sum( {$<Year = {$(=Only(Year)-1)}>} Quantity_IO)) = 0 //<= statt =
      , (sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_GM_GRIR_EURX_SOC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_GM_FCC_EURX_SOC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_AC_PCV_EURX_LSC))
      ,
      if(
      round(sum( {$<Year = {$(=Only(Year)-1)}>} GoodsRec_Quantity)) <> round(sum( {$<Year = {$(=Only(Year)-1)}>} Quantity_IO))
      ,
      if(
      (
      sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_IO_GRIR_EURX_SOC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_IO_PCV_EURX_SOC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_AC_PCV_EURX_LSC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_AC_FCC_EURX_LSC) ) = 0
      ,
      sum( {$<Year = {$(=Only(Year)-1)}>} GoodsRec_Quantity_orig)
      , (
      sum( {$<Year = {$(=Only(Year)-1)}>} GoodsRec_Quantity_orig) - sum( {$<Year = {$(=Only(Year)-1)}>} Quantity_IO_orig))
      )
      *
      only ( {$<Year = {$(=Only(Year)-1)}>} STD_PRICE_LSC)
      +
      (
      sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_IO_GRIR_EURX_SOC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_IO_PCV_EURX_SOC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_AC_PCV_EURX_LSC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_AC_FCC_EURX_LSC) ) 
      ,(
      sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_IO_GRIR_EURX_SOC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_IO_PCV_EURX_SOC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_AC_PCV_EURX_LSC) + sum( {$<Year = {$(=Only(Year)-1)}>} AMOUNT_SOC_AC_FCC_EURX_LSC) )
      )
      )
      )
      )
      )
      ,
      REF_KEY, Material ,LSC)