How to Calculate Averages Across Two Different Columns
I'm trying to get the average duration of our phone calls and chats. For rows that have a Unique Call ID, we want to use Call Duration, for rows that have a Unique Chat ID, we want to use Chat Duration. There may be multiple records with the same Chat ID or Call ID, so I've been using FIXED BY to ensure we're using unique IDs. However when I run the following Beast Mode query, I get an error saying "An invalid column was specified in your request (the column does not exist on the DataSet). "
AVG(case
when `Chat ID` is not null
then AVG(`Chat Duration`) FIXED (BY `Chat ID`)
when `Call ID` is not null
then AVG(`Call Duration`) FIXED (BY `Call ID`)
end
)
Any ideas what I might be doing wrong? The FIXED BY statements work fine when they're not in a CASE WHEN structure.
Best Answer
-
You may be running into issues because of aggregation within a case statement. You can also try the formula below to bypass the case statement.
avg(AVG(ifnull(`Chat Duration`,`Call Duration`)) FIXED (BY ifnull(`Chat ID`,`Call ID`)))
3
Answers
-
Try
case
when `Chat ID` is not null
then avg(AVG(`Chat Duration`) FIXED (BY `Chat ID`))
when `Call ID` is not null
then avg(AVG(`Call Duration`) FIXED (BY `Call ID`))
end
0 -
You may be running into issues because of aggregation within a case statement. You can also try the formula below to bypass the case statement.
avg(AVG(ifnull(`Chat Duration`,`Call Duration`)) FIXED (BY ifnull(`Chat ID`,`Call ID`)))
3 -
Thanks for the speedy response, @MichelleH - that worked like a charm!
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 293 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive