Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis for Previous Year

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)

0 Replies