Calling all Windowed Beast Mode and FIXED Function experts!

chapman · 2024-05-31T21:09:09+00:00

There was an error rendering this rich post.

I think I'm close on this but am struggling to get my expected result. Here is my end goal—a chart showing data by date, with a line for the "Best Ever" value.

Things making it tricky:

I need the date range to be dynamic, i.e. user can select different dates
I need the "best ever" value to be pulled from across the entire dataset, not just the displayed dates.
The dataset is not structured with 1 row per date. Sometimes multiple rows exist for a single date.

I have the line working using the following windowed beast mode: MAX(SUM(Total Revenue)) OVER ()however, this pulls the max from the filtered/displayed dates. I need the max from the entire dataset (all time).

If only I could add the DENY FILTER date from FIXED functions into my window, I would have what I want 🙂 but obviously it doesn't work that way. Using a FIXED function, e.g. MAX(SUM(Total Revenue) FIXED (BY date)) because I have date on the x axis; and MAX(SUM(Total Revenue) FIXED ())doesn't work either.

Any ideas that don't involve ETL? ETL isn't an option because I also need to do this for calculations and I need them to be filterable by location — in order to keep everything contextual, it needs to be done in beast mode. TIA!!!

Find more posts tagged with

Beast Mode

Accepted answers

GrantSmith

Ah, since you're displaying dates in your x-axis and you don't want the dates to be included in your amount you can use the REMOVE clause in your fixed function: https://domo-support.domo.com/s/article/4408174643607?language=en_US

All comments

GrantSmith

have you tried using your fixed function but instead of using by date use filter deny date and have no by clause

chapman

Thank you @GrantSmith — no dice.

MAX(SUM(total_revenue) FIXED (FILTER DENY date))gives me the sum of all records for all time. I need the max sum by date, but not affected by the x axis.

Maybe it's not possible

GrantSmith