topic Re: How to calculate Yesterday's Sales using YR_DAYS field from Master Calendar in Connectivity & Data Prep

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

mdoll49 — Mon, 16 May 2022 17:26:40 GMT

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!

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

vinieme12 — Tue, 17 May 2022 08:30:26 GMT

=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

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

mdoll49 — Tue, 17 May 2022 14:56:55 GMT

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?

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

vinieme12 — Tue, 17 May 2022 15:47:30 GMT

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)

)

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

mdoll49 — Tue, 17 May 2022 17:07:31 GMT

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?