Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bhargavikn
Contributor III
Contributor III

Show Current Month and Previous Month Sales based on Date selection

Hi All,

I'm trying to get previous month and current month sales measures in Qlik Sense Table.

Current month sales is working fine as per selection. But previous month sales are showing blank until I select both Current and Previous month dates.  

Example:

Product Date Sales
Apple 31-01-2023 100
Apple 28-02-2023 200
Apple 31-03-2023 300
Banana 31-01-2023 400
Banana 28-02-2023 500
Banana 31-03-2023 600

 

Expected Output:

Product Sum(Selected Date Sales)
31-03-2023
Sum(Previous Date Sales)
28-02-2023
Apple 300 200
Banana 600 400

 

Expressions tried:

Current Month Sales:  sum( TOTAL { <[Date]={"$(=date(max([Date]),'DD-MM-YYYY'))"}>}Sales)
Previous Month Sales: sum( TOTAL { <[Date]={"$(=date(floor(monthend(max([Date]),-1)),'DD-MM-YYYY'))"}>}Sales)

Issue : Getting Previous month sales only after selecting 2 months. For example, If I selected 31-05-2023, showing only Current month sales and previous month is blank. But If I select 31-05-2023 and 30-04-2023, Showing Current and Previous month sales. 

Please help me to resolve the issue using chart expressions.

Thank you.

Labels (1)
4 Replies
md_talib
Contributor III
Contributor III

You need to make two 4 variable for date function 

2 for current month and 2 for previous month 

for current Month

vCalMinDate =date(MonthStart(Max(Posting_Date)),'DD/MM/YYYY')

vCalMaxDate =date(Max(Posting_Date) ,'DD/MM/YYYY')

for Previous Month

vPMCalMinDate =date(addmonths(MonthStart(Max(Posting_Date)),-1),'DD/MM/YYYY')

vPMCalMaxDate =date(addmonths(Max(Posting_Date) ,-1),'DD/MM/YYYY')

then your selection will work perfect.

 

bhargavikn
Contributor III
Contributor III
Author

Thank you for your response. 

In data, I have only one date that is Month end date for each month and need value based on that current date and previous date.

I'm unable to understand your logic. Could you please provide sample expression.

Thank you again.

bhargavikn
Contributor III
Contributor III
Author

Hello, Can someone suggest me on the issue please.

Thank you.

md_talib
Contributor III
Contributor III

you need to create a master calendar which will create the dates for the range.