Period over Period (Business Day)

First off, I wanted to say the new YoY calculation options are a great addition and a big time saver when creating period over period cards. 


Unfortunately for us, our company metrics mainly revolve around current business days. So when building a card like this, I still need to determine the current business day and only return previous period records before the same business day (ie, This Month to Current Business Day over Last Month to the Same Business Day).  These beast mode calculations can take quite a while to set up. 


It would be great if we could possibly maintain a company calendar and have the Period to Current Business Day added to the Period over Period options available in the analyzer.

4 votes

· Last Updated


  • btm



    Thank you for submitting this @Valiant. I am assigning to our product manager @ckwright to review and comment.

  • @Valiant If you have a fiscal calendar and you use To Date information then it will give you Current Business Day vs Last Month Same Business Day. Or you can use 4wks, 12wks, 52 wks ago and that will align by the same business day. You can email me directly if you want to talk through it and if I am miss understanding your need. 

  • The same business day as we use it is a bit different than what you described. 


    Here's an example using Month over Month:

    1. My current Month is September 2017 and it's the 8th day of the month. 

        Since there was a Labor Day holiday on the 4th, that means that Sept 8, 2017 is actually the 5th business day of the month.

    2. Using Month over Month if I went back to August 2017 on the 8th, there was no holiday in this time frame and the 8th is actually the 6th business day of the month. So a month over month calculation wouldn't be accurate for something like total sales since there would've been an extra working day in August. 

  • @Valiant If you use 4 weeks ago (28 days ago) then it will match up as the same business day. So it will compare Friday wk 1 this month vs Friday wk 1 last month. It doesn't match it up by the date (1st vs 1st) but by the day ( This week Monday vs Monday last month) 

  • I understand it matches by day of week. Perhaps "Working day" is a better term. If Friday wk 1 last month was a holiday vs Friday wk 1 this month is not a holiday, then comparing those two dates throws off things like growth % since it skews the production as the underlying number of days worked in each time period is different.

  • @Valiant So what Friday would you want to compare it to? The Friday before?  Fiscal calendar solves for this in a lot of cases. You would compare YoY, you would want to compare Last Year Labor Day vs This Year Labor Day. If you are doing MoM then this gets tricky, what is a good Monday to compare Monday Labor Day to? Would you want to exclude Labor Day's results? 

  • Here's how I handled it in our environment.


    1. I created a table of all dates from Jan 1, 2000 to Jan 1, 2200 (something where I won't have to worry about a date outside of my range).

    2. I created a function in SQL called WorkingDayofMonth that looks to see if a day is a weekend or holiday and doesn't add a working day if so. 

    My result would be something like:

    Date                       Working Days This Month                Working Day?

    Sept 1, 2017                    1                                                       Y

    Sept 2, 2017                    1                                                       N (Weekend)

    Sept 3, 2017                    1                                                       N (Weekend)

    Sept 4, 2017                    1                                                       N (Holiday)

    Sept 5, 2017                    2                                                       Y

    Sept 6, 2017                    3                                                       Y

    Sept 7, 2017                    4                                                       Y

    Sept 8, 2017                    5                                                       Y

    Sept 9, 2017                    5                                                       N (Weekend)

    Sept 10, 2017                  5                                                       N (Weekend)


    So using this against my dates/sales table I can do a MoM calculation and return all days this month and last month where Working Day of Month <= 5. Comparing This month to last month now would then give me same number of working days in each month.

This discussion has been closed.