Run rate beastmode calculation

tmerchant

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)

david_cunningham

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