Calculating only the distinct values for many duplicate sessions
Hello!
I'm working on a conversation dataset that has a lot of duplicate values as our API creates a duplicate session ID for each part of the conversation someone reaches when they engage with our experience. My problem is I'm having difficulty calculating that average conversation duration because each duplicate row per session ID also has a duplicate conversation duration value, so I can't simply take the average value of that column. Is there a way that I can create a beast mode calculation that only calculates the average session duration 'durationsec' when there is a unique 'session'?
I've included an extract of my dataset that includes the duplicate session IDs and their corresponding duration metrics.
Any help would be greatly appreciated :)
Thanks!
Best Answer
-
You can use a fixed function for this:
AVG(MAX(`duration`) FIXED (BY `session`))
This will take the maximum of the duration for each session identifier and then average each of those values out. As the duration should be the same across all of the same sessions this is essentially deduping your durations.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3
Answers
-
You can use a fixed function for this:
AVG(MAX(`duration`) FIXED (BY `session`))
This will take the maximum of the duration for each session identifier and then average each of those values out. As the duration should be the same across all of the same sessions this is essentially deduping your durations.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**3 -
Thanks so much for the help, Grant! Your beastmode worked like a charm.
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
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 688 Automate
- 177 Apps
- 453 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
- 126 Manage
- 123 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