Issue with Calculating Current and Previous Month Sales in Pivot Table

[Purushothaman]

Hello Qlik Experts,

I am trying to create a pivot table in Qlik that shows "Current Sales" and "Previous Month Sales" for each "month year" selected.

However, I'm encountering issues with the accuracy of the figures for the previous month's sales, and I also need the pivot table to dynamically update based on multiple "month year" selections.

Data Structure:

Here is how my data is structured:

SalesDate	Product	Amount
10/1/2023	Chair	50
15/1/2023	Fan	100
24/2/2023	Chair	82
23/2/2023	Fan	72
14/3/2023	Chair	132
13/3/2023	Fan	56
10/1/2024	Chair	50
15/1/2024	Fan	100
24/2/2024	Chair	82
23/2/2024	Fan	72
14/3/2024	Chair	132
13/3/2024	Fan	56

Measure for Sum of Amount for Selected Month

sum({<MonthYear,Month,Year,SalesDate = {">=$(=MonthStart(Max(SalesDate)))<=$(=MonthEnd(Max(SalesDate)))"}>} Amount)

Measure for Sum of Amount for Selected Previous Month 

sum({<MonthYear,Month,Year,SalesDate = {">=$(=MonthStart(AddMonths(Max(SalesDate), -1)))<=$(=MonthEnd(AddMonths(Max(SalesDate), -1)))"}>} Amount)

Attached is the QVF. 

Thank you very much for your assistance!

[WangKun]

Try this one for Previous Month with total keyword

Measure for Sum of Amount for Selected Previous Month:

sum({<MonthYear,Month,Year,SalesDate = {">=$(=MonthStart(AddMonths(Max(SalesDate), -1)))<=$(=MonthEnd(AddMonths(Max(SalesDate), -1)))"}>} total <Product> Amount)

[Purushothaman]

Hi @WangKun ,

Below is the result for updating your measures for previous month. It fixed for March 2024 but Feb 2024 Even Current Month Sales and Previous Month Sales figures wrong.

Thank you!

[WangKun]

I try below with before function

Current Month Sales = sum(Amount)

Previous Month Sales = =if(sum(Amount)<> 0,before(sum({1}Amount),1))

then uncheck "include zero values" for pivot value. but with one issue, if no current months sales, then previous month sales will also be zero.

[Purushothaman]

Hi @WangKun ,

Thank you for your efforts, still the output is correct. I want the end result like this in a Pivot Table. 

Product	Jan-23		Feb-23		Mar-23		Jan-24		Feb-24		Mar-24
	Current Month	Previous Month	Current Month	Previous Month	Current Month	Previous Month	Current Month	Previous Month	Current Month	Previous Month	Current Month	Previous Month
Chair	50	0	82	50	132	82	50	0	82	50	132	82
Fan	100	0	72	100	56	72	100	0	72	100	56	72