Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)