Dynamic expression for Year on Year %Change

[hillarynyawate]

I have an expression in qlik sense that calculates the % change in amount from a previous year to a current year using variables vPY(previous year) vCY(current year).

The expression is as follows:

(SUM(IF(YEAR(TRANSACTION_DATE)=vCY,AMOUNT))-SUM(IF(YEAR(TRANSACTION_DATE)=vPY,AMOUNT)))
/
SUM(IF(YEAR(TRANSACTION_DATE)=vPY,AMOUNT))

I need this formula to be dynamic in such a way that it sums the amount upto the current month for example the % change between 2023 and 2024 should be only for January for each of the year.  The one for 2023 and 2022 should be for the whole year and so forth.

Currently the formula is using the whole of 2023 against January of 2024.

[Rohan]

Hi,

Try :

(Sum({<TRANSACTION_DATE={"<=$(=date(yearstart(max(TRANSACTION_DATE)))) <=$(=date(max(TRANSACTION_DATE)))"}>} Amount) - 

Sum({<TRANSACTION_DATE={"<=$(=date(yearstart(max(TRANSACTION_DATE),-1))) <=$(=date(addyears(max(TRANSACTION_DATE),-1)))"}>} Amount) )/

Sum({<TRANSACTION_DATE={"<=$(=date(yearstart(max(TRANSACTION_DATE),-1))) <=$(=date(addyears(max(TRANSACTION_DATE),-1)))"}>} Amount)

Regards,

Rohan.

[hillarynyawate]

Hi @Rohan,

Thank you for your prompt response I have tried the expression but its giving zero as the output like its getting subtracting values from themselves hence cancelling out. 

At least that's what I could imagine but I need more help on how to tweak the expression to show the values.

[MarcoImperiale_Progesa]

I think you should work with YearName, MonthName. Also create a numeric Month (MNameNum) and Year value:

IF(Max(Year)=Year(Today()),
{<MNameNum={"<=$(=(Num(Month(Today()))))"}>}(sum({<Year={$(=Max(Year(Today())))}>} AMOUNT)/sum({<Year={$(=Max(Year(Today()))-1)}>} AMOUNT)-1),
sum({<Year={$(=YearName(Today(),-1))}>} AMOUNT)/sum({<Year={$(=YearName(Today(),-2))}>} AMOUNT)
)

Hope it works for you!

M