Skip to content

    • Datasets

    Datasets

    Run rate beastmode calculation

    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:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Answers

    • 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 

      1. 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! βœ”οΈ**

    • 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?

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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories