FIXED BY referenced in another beast mode

rmbourne

Hello everyone,

I am trying to reference a beast mode (streak) to another (streak_breaker) that's using a FIXED (BY) function, but it keeps on returning an error. Is this not feasible?

Here's my example data and calculations:

1. streak_breaker = MAX(MAX(CASE WHEN status = 'draft' THEN campaign_idEND) FIXED (BY account_id)) » Gets the latest campaign that wasn't sent per account
2. streak = COUNT(CASE WHEN campaign_id > dynamic_streak_breaker THEN campaign_id END) » Counts the number of campaigns published later than the one that wasn't sent

If only this two works together, I can count the sent streak accurately even if I do not filter anything, which would result to 6, as the streak_breaker would be 4 and there are 6 campaigns more than 4, or if I filter Sponsor 1, which would result to 5, as the streak_breaker would switch to null/0

campaign_id	sponsor	status
10	sponsor2	sent
9	sponsor 1	sent
8	sponsor2	sent
7	sponsor 1	sent
6	sponsor2	sent
5	sponsor 1	sent
4	sponsor2	draft
3	sponsor 1	sent
2	sponsor2	sent
1	sponsor 1	sent

Actually, even if I changed the streak_breaker to the below formula , the streak beastmode will validate but the chart will still throw an error

MAX(CASE WHEN status = 'draft' THEN campaign_id)

GrantSmith

It’s because you have an aggregate inside an aggregate which isn’t allowed. You could attempt to use a window function so the max is returned for each row depending on your partitions and then do the count on that value

COUNT(CASE WHEN `campaign_id` > MAX(`latest_unsent_campaign_acct`) FIXED (BY `your_partition_field_or_remove_by_clause`) THEN `campaign_id` END)

Window or fixed functions will return a single value for each row which will allow you to then aggregate.