FIXED BY referenced in another beast mode

rmbourne
rmbourne Member
edited May 20 in Beast Mode

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)

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**