I'm the MajorDomo for a large retailer, and our Fiscal Calendar uses a 4-4-5 format. This means we have a Week 53 this year, and a large portion of our Domo reports are built off of a TY/LY comparison. I'm working on logic to reconfigure our reports always look 364 days or 52 weeks ago, rather than look at the same Fiscal Week the prior year.
To do this, I pulled in a "Week Number in Epoch" field onto one of our datasets that incrementally counts from the first week of 2010. The idea here was that was that I would write a Beast Mode to find the "Week Number in Epoch" value for each record, then subtract 52 weeks from value, and pull the "Net Sales" to get LY.
The problem I ran into is that any Beast Mode logic needs to compare the Epoch columns against itself, and this breaks apart quickly, as the "Week Number in Epoch" column will never equal itself minus 52. The closest I've come was using Fixed functions to always pull LY WTD, but this logic breaks apart if the user needs to look at a different Fiscal Week.
CASE WHEN `WEEK_NUMBER_IN_EPOCH` = (MAX(CASE WHEN `DATE_VALUE` = CURDATE() - 1 THEN `WEEK_NUMBER_IN_EPOCH` END) FIXED(FILTER NONE)) - 52 THEN `NET_SALES` END
This logic could work if Beast Modes could read that second CASE statement as a Temp Table, rather than rendering it across each record. For example, last week's "Week Number in Epoch" could be 1174, then the Beast Mode would isolate that value, and search other records in the same column. I should be able to accomplish something similar by adding "LY Week Number in Epoch" columns and searching for records where "LY Week Number in Epoch" = "Week Number in Epoch", but for the sake of performance, I would rather do this in one column in possible.
Does anyone know if Beast Modes have this sort of functionality? Or has anyone else struggled with Week 53 logic and found a better solution? I'm open to input. Thank you in advance for your time!