Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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!
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.
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 |