Calling all Windowed Beast Mode and FIXED Function experts!

Options
chapman
chapman Member
edited July 7 in Calculations & Variables (Beast Mode)

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.

image.png

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!!!

Tagged:

Best Answer

Answers

  • GrantSmith
    GrantSmith Coach

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

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

    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
    GrantSmith Coach
    Answer ✓

    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

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

Categories