# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results 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...

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
Community Browser