Calling all Windowed Beast Mode and FIXED Function experts!

chapman

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:

1. I need the date range to be dynamic, i.e. user can select different dates
2. I need the "best ever" value to be pulled from across the entire dataset, not just the displayed dates.
3. 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!!!

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

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

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