Number of week calculations for fiscal YYWW (Year, Week of year)

BrentJensen

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!

GrantSmith

What I'd recommend is establishing a new date dimension dataset which has each of your fiscal week values for each of the dates, you can then join this to your dataset to have the correct fiscal week number

GrantSmith

What I'd recommend is establishing a new date dimension dataset which has each of your fiscal week values for each of the dates, you can then join this to your dataset to have the correct fiscal week number

MarkSnodgrass

I think your easiest option is to join your data with the calendar dataset in the Domo Dimensions calendar. If you haven't used this dataset, it is very useful and contains information about every date for future and past dates.

I would filter the calendar dataset to where dayofweek = 1 so that you get one entry per week for every year. You would then split your yearweek column into a year column and a week column and then join it to the calendar dataset on those two fields. The calendar dataset will give you the actual date for that yearweek that you can then use in your cards.

Let me know if you need me to explain that further.

DavidChurchman

I think the math isn't that hard to get week differences, you just need to multiply the years by 52.

( LEFT(YYWW_date_1, 2)*52 + RIGHT(YYWW_date_1, 2)) - ( LEFT(YYWW_date_2, 2)*52 + RIGHT(YYWW_date_2, 2))

For your 2301 - 2244 example, that would be (23*52+1) - (22*52 +44) = (1197) - (1188) = 9

Caveat: I'm pretty sure multiplying by 52 forces that from a string to numeric, but not positive. You might have to do something else to force those lefts/rights to integers

BrentJensen

Thank you @GrantSmith - Your advice is what solved this for me.

As suggested by GrantSmith, I created an excel upload dataset with a file that references our 4 digit YYWW to every calendar date in that week. Here is a sample of the spreadsheet used to create the date conversion dataset:

The dataset was created by uploading the excel to DOMO:

The card I was creating was already using a Magic ETL dataset:

At the bottom, you can see where the date conversion dataset is coming in to the existing dataset that was already almost what was needed. DOMO didn’t automatically identify the MMDDYYYY fields coming in as dates, so I put in the “Alter Columns” module to change the properties of these fields to an actual date data type:

Since there were 2 YYWW fields to be compared, added 2 joins. Each join was configured similarly to associate a YYWW field from the main data to the newly brought in MMDDYYY date formatted data:

Important note: With the second join there was a warning that there were duplicate field names, which forced renaming them to something different on the 2nd join. I chose to name the field in a way to reference to the respective column to make it easier to tell the difference later.

Used a Select columns module to rename and move the new columns to a place that made them easy to use later and rename them:

Looking at the dataset, there are now 2 places where there is a MMDDYYY date field which correctly corresponds to the YYWW date in the neighboring column – Note, I chose the Sunday day of the week on both as our systems update on Sunday making it the most relevant date to choose – any date would have worked though as we are only trying to get a count of the difference in the number of weeks.

Which now makes the beast mode super easy to write for the card:

And gives a result we can display and use for filtering:

Thanks again for pointing me in the right direction, Grant!

Brent Jensen

Bass Pro Shops

Merchandise Operations Analyst