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.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 282 Workbench
- 3 Cloud Amplifier
- 4 Federated
- 2.8K Transform
- 86 SQL DataFlows
- 548 Datasets
- 2.2K Magic ETL
- 3.2K Visualize
- 2.3K Charting
- 543 Beast Mode
- App Studio
- 26 Variables
- 566 Automate
- 134 Apps
- 411 APIs & Domo Developer
- 21 Workflows
- DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 344 Distribute
- 86 Domo Everywhere
- 257 Scheduled Reports
- 1 Software Integrations
- 85 Manage
- 84 Governance & Security
- 6 Product Release Questions
- Community Forums
- 41 Getting Started
- 27 Community Member Introductions
- 81 Community Announcements
- 4.8K Archive