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?