Granularity Issues in Domo
Hello, I have a dataset that I'm working with that has my data at a granularity that is quite large.
I then want to be able to group that by each individual session that happens on our website and sum the total time spent for that session. I then want to take the average across all sessions. But I want to be able to do that dynamically dependent on what the user filters.
For example, I want a user to be able to filter to a particular country (say United States) and for it to sum the total time spent on the website for each session under that country and THEN to take the average across all sessions. But that filter will change dependent on what the user will filter on the page and so I can't hard-code it into the ETL but needs to be a beastmode.
I thought of first doing a window function but I couldn't figure out how to get it to work in this scenario since I'm changing the granularity of the data a couple times from the original granularity. Any ideas?
Best Answer
-
You could use either a Window Function (external video) or the Fixed function, but Beast Modes are no good to do aggregation of aggregations. If the aggregation will happen at a single level, then on either the OVER clause of your window function or the FIXED clause of the fixed function, instead of using a specific field, you'll enter the logic on the granularity selected.
Another option is to do some aggregation first at ETL (most likely with a view) to the highest level of granularity that still contains all the data detail you need for this, and then just roll it up with beast modes or depending on the base card.
If you could provide a sanitized sample of what the raw data looks like and what the ideal outcome would look like that could help get a better sense on the available options.
0
Answers
-
You could use either a Window Function (external video) or the Fixed function, but Beast Modes are no good to do aggregation of aggregations. If the aggregation will happen at a single level, then on either the OVER clause of your window function or the FIXED clause of the fixed function, instead of using a specific field, you'll enter the logic on the granularity selected.
Another option is to do some aggregation first at ETL (most likely with a view) to the highest level of granularity that still contains all the data detail you need for this, and then just roll it up with beast modes or depending on the base card.
If you could provide a sanitized sample of what the raw data looks like and what the ideal outcome would look like that could help get a better sense on the available options.
0 -
Hey Marcel! Thank you! A fixed function solved my issue! I obviously need to do some more research into Fixed functions because this honestly is amazing how it solves my issue:
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 623 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 690 Automate
- 177 Apps
- 455 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive