Beast mode referencing

rmbourne
rmbourne Member
in Beast Mode

Hello,

I wonder why this won't work.

image.png

I get that the CASE WHEN can't have MAX() within it, but I thought by referencing the beast mode that uses the MAX() would process it as a subquery that references the output value instead of the formula MAX().

If this wouldn't work, is there an alternative to this? I needed the latest_unsent_campaign_acct to be dynamic that's why I brought it to the card before wrapping it with MAX()

Tagged:

Answers

  • GrantSmith
    GrantSmith Coach

    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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • rmbourne
    rmbourne Member
    edited May 2024

    Hi @GrantSmith

    Thanks. I tried but it doesn't work either. The use of partition was actually my initial approach, but I used MAX(MAX() FIXED (BY)) following the documentation on FIXED function. Neither of them worked.

    image.png
    image.png

    It turns out, even the partition can't be within an aggregation as it works if I removed the COUNT()

    image.png
    image.png

Categories