Comparing weekly data

ArborRose

Say I want to compare 2023 and 2024 March data. In 2023, March starts on a Wednesday. In 2024, March starts on a Friday. If I chart day of month, I can visually see the weekend gaps.

I'd like to find a way to compare weeks to the previous year, that executives could easily understand. But because of the movement of the calendar days, this is problematic. How do y'all handle this? Slide the years to match up?

I want the world to adopt a perpetual calendar.

Here's my proposal:

1. Month Structure:
   1. Each month has 28 days.
   2. There are 13 months in total.
2. Leap Year Rule:
   • Every 4th year is a leap year, adding an additional day at the end of the year. This day is added every four years to account for the extra time in the Earth's orbit. It is not considered part of any month and is treated as a standalone day. In leap years, the 13th month would have 29 days instead of 28, with Leap Day added at the end.
3. Day Alignment:
   • January 1 always starts on a Sunday.
   • December always ends on a Saturday.

With this calendar, let's see how it would look:

• Every year would have 364 days (13 months * 28 days).
• In a leap year, there would be an additional day at the end, making it 365 days.
• January 1 of one year would always match the day of the week with January 1 of the next year.

For example:

• If January 1, 2024, is a Sunday, then January 1, 2025, would also be a Sunday.
• If December 28, 2024, is a Saturday, then December 28, 2025, would also be a Saturday.

And leap day would be a memorial day with no sales tax, big discounts, and excessive partying.

MichelleH

@ArborRose What if you used the isWeekday and isHoliday fields in the Domo Dimensions Calendar dataset to create a "Working Day of Month" dimension that you use for the X-Axis in your cards? You should be able to apply a rank window function partitioned by month and year to all the non-holiday weekdays to come up with that new field

marcel_luthi

Totally agree, this is a headeache because the 1st week of each month does not have the same number of business days in it, and this clearly affects your metrics. The hardest part would be to come up with a name that everyone would agree for that standalone day, as it'll also need to be it's own standalone month as for not to give any special treatment to any of the other 13 🤔 (don't tell the others but I do have a preference for one of them already).

As for reporting right now, as soon as someone says they want to see weekly things in a Year over Year fashion, I tell them they'll only be able to see it as a comparison against 52 weeks ago, and that you cannot have a weekly view that aligns to month and year because of how Gregorian Calendar works 😅

ArborRose

@marcel_luthi. Oh….what's your name preference?

MichelleH

@ArborRose What if you used the isWeekday and isHoliday fields in the Domo Dimensions Calendar dataset to create a "Working Day of Month" dimension that you use for the X-Axis in your cards? You should be able to apply a rank window function partitioned by month and year to all the non-holiday weekdays to come up with that new field

ArborRose

@MichelleH - I'm not sure what you mean. Can you explain further or point me to some kind of example?

ArborRose

If I comprehend the suggestion correctly - create a month of working days. Exclude holidays and weekends. So you have from 1 to however many useful days exist in a month. Use rank to number them. And chart those in comparison? So for this month, March 2024, we would have 21 working days. I'm not sure what the Domo Dimensions Calendar refers to.

MichelleH

@ArborRose That's correct. The reason I mentioned the Domo Dimension Connector is that the "calendar.csv" report includes fields to indicate whether each day is a weekday or holiday, which you could use to exclude non-working days from your ranking

ArborRose

Something like this maybe?

CASE
WHEN WEEKDAY(appointment_date) IN (2, 3, 4, 5, 6)
AND CONCAT(MONTHNAME(appointment_date), ' ', DAYOFMONTH(appointment_date))
NOT IN ('January 1', 'July 4', 'December 25')
THEN 'Working Day'
ELSE 'Non-Working Day'
END

ArborRose

Oh….I misread. Domo Dimensions Connector. I'm not familiar with it. I have some learning to do. Thanks.

marcel_luthi

I like @MichelleH approach. I guess the only question after is what should you do if you happen to have an entry on a weekend for some weird reason, should you those be pushed to the next working day or what? (Hopefully this doesn't happen but is good to prepare for it).

If I'm following her correctly after the work is done, you'll have a "WorkingCalendarDate" that will go from the 1st of the month to the Nth of the month where N is the number of working days in that given one, sounds like a fun and interesting ETL to build 🤔 (might try to do that one of these days)

ArborRose

@marcel_luthi - I pondered that issue about a weekend action. In the data I'm working in today, it's not an issue and I can throw it out. But you could put some condition in your calculation.

I haven't been able to look back on this formula as my day is getting crowded with other projects. But here's where I was last on my attempt at a formula…