How to graph/program difference from average without hardcoding time frame?
We have a dataset that has 1 survey per row. Our ultimate end target is to show difference from average score by employee for varying time frames.
For example if the survey score overall average was 85 and the average of John Smith's surveys was 88 we want to show +3.
This can easily be programmed in and hard coded in ETLs if there is a set time frame you want to calculate the average over. However, ideally we would like the time frame to be able to be set/picked by the end user of the dashboard. For example to be able to see difference from average Last Week or Last Month or YTD based on the user's selection. This is where we are getting stuck. At the beast mode/card level i am not sure how to both calculate the Overall Average while also segmenting it by Rep so that we can calculate the Individual's Average. Any ideas on how to handle this?
I hope that explanation of what we're trying to do makes sense. Thanks!
Comments
-
Super easy!
Just UNION the data onto itself. And then replace the rep's name in the second copy with "all reps" then use a Window Function in Analyzer
sum(score) /
sum(sum(case when rep = 'all reps' then score end )) over ()
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
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 477 Transform
- 1.8K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 218 Visualize
- 260 Beast Mode
- 2.1K Charting
- 12 Variables
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 249 Distribute
- 65 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 191 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive