Calculate the difference - Current Month Current Year Vs Previous Month last Year

[Narz]

Hi all,

In a straight table,

 1. I need to show the current Year Sales by Month. Sum(Sales)

 2. The second column calculating the difference between current and previous month.

  Example: Feb will show Feb 2024 - Jan 2024 Sales as Sales Difference. 

This works fine for all the months except January. 

  3. Facing difficulty in calculating for Jan where the difference has to be calculated for Jan 2024 and Dec 2023.

 4. The 

if (Month='JAN' and Year=2024, Sum({<Year = {"$(=Max(Year)-1)"}, Month = {"DEC"}>} Sales))

I get 0. I am able to achieve Current and Previous Year same month.

Checked and also tried this thread and still not able to achieve the expected output: https://community.qlik.com/t5/App-Development/pulling-previous-years-data-while-current-year-selecte...

Can somebody help with previous month previous year?

[Narz]

I get the same empty date values.

Outside Set Expression, I try -

MonthStart(AddMonths([BillingDateNum], -1))

I get the output as below, when used inside the expression it retuning blank.

[steeefan]

What happens when you wrap the MonthStart() and MonthEnd() function calls in Num()?

[Narz]

I am not able to get the result.

[steeefan]

I'm at a loss. This should work, totally works for me. Are you sure that BillingDateNum is a numeric representation of your date?

What happens when you add =Num(MonthStart(AddMonths(Num(BillingDateNum), -1))) as an expression to your table?

Try also this:

Sum({<BillingDateNum = {">=$(=Num(MonthStart(AddMonths(Num(BillingDateNum), -1))))<=$(=Num(MonthEnd(AddMonths(Num(BillingDateNum), -1))))"}, Year =>} Sales) 

[Narz]

Yes, BillingDateNum is numeric representation of date. I did include the screenshot of my model earlier which shows numeric representation of date.

Sum({<BillingDateNum = {">=$(=Num(MonthStart(AddMonths(Num(BillingDateNum), -1))))<=$(=Num(MonthEnd(AddMonths(Num(BillingDateNum), -1))))"}, Year =>} Sales) 

Outsite set expression I am able to get the result 

[steeefan]

Sorry, no clue. Hard to say remotely. Would you be able to upload your QVF with a reduced and anonymized data set?

[lennart_mo]

Looking back at your initial formula i noticed that you don't need to add the 1 to the set expression but rather the total qualifier to the Sum(), since it's the available data for the sum() is limited, the set expression already gets all the data it needs.

if(Month='JAN' and Year=2024, Sum({<Year = {"$(=Max(Year)-1)"}, Month = {"DEC"}>}TOTAL Sales))

Tried it for mysefl in a similar case, so I'm hoping it'll work for you too!