Filter Removes Summary Number
I created the following beastmode for my summary number. The beastmode works and returns the summary I am looking for. However, when I change the filter on the dashboard (ie to look at a specific customer instead of all encompassing) the summary number on my card turns to zero. Any ideas how I can get my summary number to reflect the filter I am placing on the dashboard?
CONCAT('<div style= "Font-size:13.5px">',
ROUND(abs(AVG(CASE
when LOWER(`On-Time or Late`) = 'on-time' and `New drop/live`= 'Live' then (`Live Turn Time Data`)
when LOWER(`On-Time or Late`) = 'late' and `New drop/live` = 'Live' then (`Live Late Turn Time Data`)
END))), ' AVG "Live" Turn Time (Min) |',
ROUND(abs(AVG(CASE
when LOWER(`On-Time or Late`) = 'on-time' and `New drop/live` = 'Drop' then (`Drop Turn Time Data`)
when LOWER(`On-Time or Late`) = 'late' and `New drop/live` = 'Drop' then (`Drop Late Turn Time Data`)END))), ' AVG "Drop" Turn Time (Min)')
Comments
-
I have to think that you are getting NULL values for your rounded numbers. Such as where there are no records that match your case statements when you filter.
It would be quick to test out. Try wrapping the numeric calculations in an "IFNULL(...,0)" function to default nulls to zero. Like this:
- CONCAT(
'<div style= "Font-size:13.5px">'
,IFNULL(
ROUND(
ABS(
AVG(
CASE
WHEN LOWER(`On-Time or Late`) = 'on-time' AND `New drop/live`= 'Live'
THEN (`Live Turn Time Data`)
WHEN LOWER(`On-Time or Late`) = 'late' AND `New drop/live` = 'Live'
THEN (`Live Late Turn Time Data`)
END
)
),0
)
), ' AVG "Live" Turn Time (Min) |'
,IFNULL(
ROUND(
ABS(
AVG(
CASE
WHEN LOWER(`On-Time or Late`) = 'on-time' AND `New drop/live` = 'Drop'
THEN (`Drop Turn Time Data`)
WHEN LOWER(`On-Time or Late`) = 'late' AND `New drop/live` = 'Drop'
THEN (`Drop Late Turn Time Data`)
END
)
),0
)
), ' AVG "Drop" Turn Time (Min)'
)
Note: Do NOT add "ELSE 0" to your case statements without considering the effect it will have on your AVG calculation.
- ie: the average of 5,10,15,null,null is 10
- It becomes 30 / 3 because there are only 3 numbers
- ie: the average of 5,10,15,0,0 is 6
- It becomes 30 / 5 because zero is a real number and thus part of the average
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 - CONCAT(
Welcome!
Welcome!
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
- 659 Datasets
- 116 SQL DataFlows
- 2.2K Magic ETL
- 815 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 82 App Studio
- 45 Variables
- 776 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 82 Workflows
- 23 Code Engine
- 40 AI and Machine Learning
- 20 AI Chat
- 1 AI Playground
- 1 AI Projects and Models
- 18 Jupyter Workspaces
- 410 Distribute
- 120 Domo Everywhere
- 280 Scheduled Reports
- 10 Software Integrations
- 144 Manage
- 140 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