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