Set Analysis for Previous Year

Wed, 03 Feb 2016 13:32:31 GMT

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!

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)

topic Set Analysis for Previous Year in QlikView

Set Analysis for Previous Year