Qlik Community

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
mdoll49
Contributor II
Contributor II

How to calculate Yesterday's Sales using YR_DAYS field from Master Calendar

We use a master calendar as shown below. I need to know how to use set analysis to calculate Sum(Sales) for Yesterday's sales using the YR_DAYS field in the below image. Using this field, I'd like to be able to sum Friday, Saturday and Sunday combined as "Yesterday" on a given Monday. Each other day would simply calculate the day prior. Any help would be greatly appreciated!

mdoll49_0-1652721052183.png

 

Labels (1)
4 Replies
vinieme12
Champion III
Champion III

=if(Day(Today()='Mon'

,sum({<INVOIDE_DATE={">=$(=Date(Today()-3,'YYYY-MM-DD 00:00:00'))<=$(=Date(Today()-1,'YYYY-MM-DD 00:00:00'))"}>}Sales)

,

sum({<INVOIDE_DATE={"$(=Date(Today()-1,'YYYY-MM-DD 00:00:00'))"}>}Sales)

)

 

I would suggest you remove the time part from the INVOICE_DATE field as below

Date(Floor(INVOICE_DATE)) as INVOICE_DATE

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
mdoll49
Contributor II
Contributor II
Author

Thank you for your feedback. While this solution would work in general, our master calendar is designed to handle US holidays. Any way to use the YR_DAYS field in the above table?

vinieme12
Champion III
Champion III

 

Assuming. Latest date available is today's date then max(yr_days,2) will return yesterdays date i.e. second max value

If the laste date available in your dataset is yesterday, then simply use max(yr_days) For yesterdays date

 

Here I've assumed data is available until yesterday

 

=if(Day(today())='Mon'

,sum({<INVOIDE_DATE={">=max(YR_DAYS,3)<=max(YR_DAYS)"}>}Sales)

,

sum({<INVOIDE_DATE={"=MAX(YR_DAYS)"}>}Sales)

)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
mdoll49
Contributor II
Contributor II
Author

Unfortunately, the MAX(YR_DAYS) would always yield 254 for the total number of selling days from the calendar. 

Is there a way to do some kind of lookup on the invoice date to pull in the YR_DAYS that correspond?