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?

[steeefan]

What's your expression for "Feb will show Feb 2024 - Jan 2024 Sales as Sales Difference."? Comparing 2023-12 to 2024-01 should work the same.

Sum({<%Date = {">=$(=MonthStart(AddMonths(%Date), -1))<=$(=MonthEnd(AddMonths(%Date), -1))"}>} Sales)

This should always give you the sales of the previous month, no matter if January, February, or December. There's also other ways but give this a try first.

[Narz]

Hi,

For other months I am using the Above(Sum(Sales)). Since I have the year 2024 selected and I have data in the previous column that works. Only for Jan not able to achieve the expected result.

if(Month='JAN',
Sum({<[Billing Date] = {">=$(=Date(AddMonths(Min([Billing Date]), -1), 'MM/DD/YYYY'))
<=$(=Date(MonthEnd(AddMonths(Min([Billing Date]),-1))))"}>} Sales)
, Above(Sum(Sales)) 
)

The above expression fetch the date correctly yet data is not populated as expected.

The output I get is as below:

Please advise. 

Thanks.

[steeefan]

What happens if you remove the If(Month='JAN') part of that expression? There is no need for it. You could also try adding Year = to ignore that selection.

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

[lennart_mo]

Hi @Narz,

in your screenshot i can see, that you have the year 2024 selected. Due to that data from other years will not be used for calculations. You should be able to work around this by adding a 1 at the start of your set expression like so:

{1<>}

This implies, that all available data, regardless of selections, shall be used for the calculation.

Hope this resolves your issue!

[Narz]

Hi,

I did try the solution in the thread earlier and it didn't get me the date

Sum({<[Billing Date] = {">=$(=MonthStart(AddMonths([Billing Date], -1)))<=$(=MonthEnd(AddMonths([Billing Date], -1)))"}, Year =>} Sales)

I did try formating my Date in script if in case that may be the cause. Still I am not able to get and hence I replaced with the current expression.

Date("Billing Month", 'M/D/YYYY') as [Billing Date],

Apologies I missed to mention this in my previous reply.

[Narz]

Hi I tried adding 1 in the expression and the result I get is as below

[steeefan]

For starters, try comparing a date field that's only a number, no string such as "01.01.2023" or "1/1/2023", just 44927. Then get rid of the whole Above() thing and the check for 'JAN'.

// In your script
Num([Billing Date]) AS BillingDateNum

// In your expression
// Fixed, had two typos earlier
Sum({<BillingDateNum = {">=$(=MonthStart(AddMonths(BillingDateNum, -1)))<=$(=MonthEnd(AddMonths(BillingDateNum, -1)))"}, Year =>} Sales)

Result:

[Narz]

I copy pasted and still I get the same issue. 

Added Number conversion in script and my model looks like this,

Expression:

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

Not sure, why unable to get the dates.

[steeefan]

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

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