Calling all Windowed Beast Mode and FIXED Function experts!
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!!!
Best 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:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
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!**0 -
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
0 -
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:
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 603 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 697 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive