In our DOMO instance, there are many datasets where we express the date as the Year and Week of year based on our fiscal calendar represented as a string. See attached graphic:
For example, with today being October 25, 2022, our fiscal calendar says we are operating in week 2244 of this year. Year = 2022, week of year is number 44.
I've tried to read all through the knowledge base to find ways to calculate number of weeks between two dates using our date format and have not been able to find any.
This would be no problem to use regular math to find the difference between, for example, week 2244 and week 2250. Simple math, 2250 minus 2244 equals 6 weeks.
This becomes a problem when the two dates are in different fiscal years, for example, week 2244 and week 2301. Simple math doesn't work. 2301 minus 2244 equals 2057 when the desired result is 10 weeks.
This is a particularly good example because many will look at a calendar and say this difference is really 9 weeks.
It is an oddity this year that our fiscal calendar this year has 53 weeks (which I think only happens once every 7 years???), so the difference is 10 weeks. See the attached graphic to demonstrate.
What we need to do, and what I've been having trouble finding a way to do is convert the YYWW format from a string to a date so that we can use a beast mode command to calculate the difference between the two dates, expressed in weeks.
I've tried a number of combinations using DATE_FORMAT and STR_TO_DATE in combination with the ISO week number of the year (%v) as DATE_FORMAT(`!WEEK TO ADJUST TO`,'%Y%v') or STR_TO_DATE(`!WEEK TO ADJUST TO`,'%Y%v') but am not having any success.
Any help is appreciated.
Thanks!
