Is there a way to aggregate the output using a FIXED function used in a CASE statement?
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?
you could materialize your FIXED function as a column on your dataset by implementing it in DatasetViews. It seems like you need MAX(MAX() ) because you're trying to ascertain "current_week_num" , that's static and probably doesn't have to respond to filter criteria, so just add it as a column.Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
@jaeW_at_Onyx , I did consider that approach, and I agree that it would work. I'm just hesitant to build views on top of all my append/partition datasets solely for the "Current Week in Epoch" column. From a governance perspective, it just feels like more "stuff" to keep track of, but if that's the only way, I can make it work.
Out of curiosity, do you have any thoughts as to why enclosing the calculation within a SUM doesn't work? I think the logic should check out, but I'm wondering if maybe it's too much calculating for Domo to handle in a dynamic Beast Mode.
By the way, I have seen some of your videos, and they're pretty informative. Keep it up!0
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 477 Transform
- 1.8K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 218 Visualize
- 260 Beast Mode
- 2.1K Charting
- 12 Variables
- 88 Cards, Dashboards, Stories
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 249 Distribute
- 65 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 191 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive