How to calculate week numbers based on custom week start day

Question Summary: I want to calculate the week number from a date based on a custom week start day. How can I do this, either in Beast Mode or SQL data transforms.

Explanation:

My company Domo settings have Weeks defined from Sunday to Saturday. However, one of my internal client team records weeks from Friday to Thursday. I don't want to change my overall Domo week settings.

Consequently, when I make a card in Domo for my above-mentioned client team and group it by weeks (in date filter, or via presets in Domo calendar filter), wrong weeks are given (from Sunday to Saturday).

I've realized on of the solution can be to make a custom column/field using MySQL/Best-mode in my dataset that calculates week numbers based on start day of Friday. e.g. for 2020, 1st and 2nd Jan was Wed and Thursday, so those days would be in Week-1, 2020. After that, it would be Week 2, 2020 and so on. I would then concatenate the weeks and years to make a new columns, and use these instead of dates to represent time

I don't know as of now how to do this calculation. Can anyone help me?

Find more posts tagged with

Comments

GrantSmith

Hi @hamza_123

Have you thought about using a beast mode shifting the dates either ahead 2 days or back 5 days (depending on how you want to calculate the week number) and then graphing by week using the new shifted date?

DATE_ADD(`Date`, 2)
DATE_ADD(`Date`, -5)

If you have your Domo settings to be displaying the week with the date this won't work as the date would be wrong (offset) but if you're displaying the week numbers themselves it should work.

If that doesn't get you what you need then you'd have to write a more complex beast mode to calculate the week number.

jaeW_at_Onyx

Tricky! But dangerous if the changes are not transparent.

Consider using the fiscal calendar .https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/KPI_Card_Building_Part_2%3A_The_Analyzer/Using_A_Fiscal_Calendar

Keep in mind, using a fiscal calendar DOES NOT change how DATE functions in Beast modes evaluate. It only implacts the Date filters in cards.

@GrantSmith wrote:
Hi @hamza_123

Have you thought about using a beast mode shifting the dates either ahead 2 days or back 5 days (depending on how you want to calculate the week number) and then graphing by week using the new shifted date?
DATE_ADD(`Date`, 2)
DATE_ADD(`Date`, -5)
If you have your Domo settings to be displaying the week with the date this won't work as the date would be wrong (offset) but if you're displaying the week numbers themselves it should work.

If that doesn't get you what you need then you'd have to write a more complex beast mode to calculate the week number.

hamza_123

thank you for the replies.

@jaeW_at_Onyx if I get it correctly, a fiscal calendar option can be set up in the backend that will have seperate calendar options (e.g. week start days and year start/end) than the overall company settings? And this fiscal calendar can be applied individually to cards.

The link you provided was a little vague so just clarifying