How to calculate week numbers based on custom week start day

hamza_123

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?

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.