Dynamic Index
Hello,
I am trying to standardize multiple metrics over time by creating an index, on a max 100pt scale. Ideally, the index value would change when user changes the date range. The attached spreadsheet shows what I'm hoping to accomplish. I'm trying to do this through beast mode calculation rather than ETL.
Thanks in advance for the help!
Comments
-
Hi @user10540
This is simple enough with a window function in a beast mode. From looking at your spreadsheet you're essentially looking to see what % of the maximum number within your date range is. This is a great case for window functions. This is a feature switch so if you don't currently have it you'll need to talk to your CSM to get it enabled.
`X` / MAX(MAX(`X`)) OVER ()
This will get you a decimal representation of a percentage. You can then just format your column as a percentage with 0 decimals to line up to what you're showing in the excel document for format. I tend to leave percentages in decimal form to allow the card creator / designer to determine how many decimal places they'd like to show rather than assuming they want 0. Give them more flexibility.
You can do the same for the Y index:
`Y` / MAX(MAX(`Y`)) OVER ()
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
At Grant,
I'm not an expert on the indexing thing but I believe MAX(SUM(X)) over () would be accurate for all use cases, whereas
Max(Max(x)) would be wrong if you're not showing the data in the chart at the row level. Example if there were two or three transactions per date, Max(Max(() would show the maximum of the max value per day, not the max of the total value per day. (which would mess up the index).
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 708 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive