Is there a way to aggregate the output using a FIXED function used in a CASE statement?

adeshon
adeshon Member
edited March 2023 in Beast Mode

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.):

CURRENT CARD.PNG

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.

WITH EPOCH.PNG

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?

Tagged:

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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"
  • adeshon
    adeshon Member

    @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!

  • ljos_14
    ljos_14 Member

    @adeshon Did you even find a solution for this? I am currently in the same debacle.

  • nick_harper
    nick_harper Member

    Bumping this one up again, does anyone have a solution to this? It is quite frustrating that you cannot aggregate the results of FIXED functions. This is very simple to do in Tableau—I have found Domo's LOD expressions extremely less useful than Tableau's 😔

Categories