I'm a MajorDomo in a large retailer, and I'm working to rewrite our TY/LY Beast Modes to calculate off of a "Week Number in Epoch" column, rather than comparing against Fiscal Week as we move into a Week 53. I previously asked about writing a temp table in a Beast Mode (https://dojo.domo.com/main/discussion/comment/59532), but I think this is the real question I have. I wrote a calculation that fixes current "Week Number in Epoch" and compares that against other records on the dataset to return sales.
Here is an example of a card where the "WTD SALES LY" column compares against Fiscal Year and Fiscal Week. I'm trying to convert such calculations to compare against "Week Number in Epoch". (Note that all data has been randomized.):
Here is the Beast Mode I wrote:
CASE WHEN `WEEK_NUMBER_IN_EPOCH` = MAX(MAX(CASE WHEN `DATE_VALUE` = CURDATE() - 1 THEN `WEEK_NUMBER_IN_EPOCH` END) FIXED(FILTER NONE)) - 52 THEN `NET_SALES` else 0 END
The problem I ran into is that the calculation seems to work, but it doesn't aggregate, and Analyzer won't give me an option to aggregate the EPOCH column. As a result, the card is broken down by date, which isn't useful in this case. I think Analyzer is reading that there is an aggregation in the Beast Mode, which is necessary for FIXED functions, but I'm not sure how to tell it to sum the output.
I also tried running a SUM on the Beast Mode itself, but that caused the card to fail in loading. I thought about adding a static "Current Week in Epoch" column, but that would fail for Append and Partitioned datasets, and I would prefer one calculation that works across the instance.
Has anyone else in the Domo community attempted something like this using aggregates of Beast Modes with a FIXED function in the logic?
