# Connectivity & Data Prep

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for
Did you mean:
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!

Labels (1)
• ### Data Prep

4 Replies
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.
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?

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.
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?