Run rate beastmode calculation

tmerchant
tmerchant Member
in Datasets

Hi all, trying to formulate a beastmode calculation to give me weekly run rate so I can do a WoW chart. No matter how I modify the query, I keep getting a "Calculation Error : All fields must be aggregated or in the group by clause". This is the first time I am seeing this error. Any insights and advice would be appreciated. Query is below, let me know if there are any glaring flaws that I'm just not seeing.

SUM(CASE WHEN Category = 'Revenue' AND YEARWEEK(Date) = YEARWEEK(CURRENT_DATE()) THEN Amount ELSE 0 END) /
COUNT(DISTINCT CASE WHEN Category = 'Revenue' AND YEARWEEK(Date) = YEARWEEK(CURRENT_DATE()) THEN Date END)

Tagged:

Answers

  • david_cunningham
    david_cunningham Coach

    @tmerchant I just tried your beast mode using some sample data, and didn't run into the same error. With that being said, one thing I would recommend is to break your beast mode into parts, and test those parts to figure out where the problem is originating from. For example, take 

    SUM(CASE WHEN Category = 'Revenue' AND YEARWEEK(Date) = YEARWEEK(CURRENT_DATE()) THEN Amount ELSE 0 END)

    and run it by itself. If that works, then do the same for the second part of the beast mode. Once you identify the source, you can do some more digging to figure out what's causing the issue.

    The error doesn't really make it sound like this is the issue - but also be sure to check that the columns you're referencing are the correct type. E.g. - Amount is numeric, date is actually a date field.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • tmerchant
    tmerchant Member

    hi @david_cunningham thank you for your reply! tried breaking it up and am immediately running into Calculation Error : Aggregate functions are not allowed in this context: 'SUM'. which is another error I'm seeing for the very first time. I know this is a typical error for WHERE nested within SUM, which isn't the case here. Any idea why this might be?

  • JacobFolsom
    JacobFolsom Domo Employee

    @tmerchant this error I would guess is coming from attempting to do the calculation in a Magic ETL formula tile, which does not allow aggregation. It will run in a beastmode on a card as @david_cunningham tested. If you want this to be in a Magic ETL formula, use a Group By tile instead, select the columns groupings and define the formula in that field. Try that and let us know!

    image.png

    Jacob Folsom
    **Say “Thanks” by clicking the “heart” in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories