Dynamic aggregation based on user selected date grain


I have a dataset that appends daily to build out a historical view. I want to be able to always show the latest record per date grain based upon the user selected date range and grain.
For example, if a user selected YTD by month, then I would want to show the following values from the last day of each month
SUM(values) from Jan 31st snapshot (ignore all other snapshots in Jan).
SUM(values) from Feb 28th snapshot (ignore all other snapshots in Feb).
SUM(values) from March 31st snapshot (ignore all other snapshots - in Mar), etc.
If a user selected current YTD by Week, I would want to show the following values from the last available snapshot of each week.
SUM(values) from the last day of week 1 (ignore all other snapshots in week1)
SUM(values) from the last day of week 2 (ignore all other snapshots in week2, etc.
I want to avoid doing this in ETL if possible, because I want it to be as responsive as possible and work across a variety of user selected date ranges/grains.
I've been playing around with window/fixed functions, but can't seem to get them to work. Details below
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**
Best Answer
-
Beastmodes don't have access to the selected date grain on the date selector so you won't be able to do it that way. You could utilize a variable to select the date grain which you would have access to.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
Beastmodes don't have access to the selected date grain on the date selector so you won't be able to do it that way. You could utilize a variable to select the date grain which you would have access to.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Having the date selector values available as system variables is exactly the kind of thing I was hoping for when I made this enhancement request, please vote for it if you're interested in seeing this functionality available.
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 656 Datasets
- 115 SQL DataFlows
- 2.2K Magic ETL
- 813 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 81 App Studio
- 45 Variables
- 771 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 77 Workflows
- 23 Code Engine
- 36 AI and Machine Learning
- 19 AI Chat
- AI Playground
- AI Projects and Models
- 17 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 142 Manage
- 138 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive