Dividing two integer columns that contain 0 values causing incorrect percentage value display (CTR)
Hi all,
I've run into an "issue" where I can't seem to get Average/AVG values to calculate correctly when using a custom beast mode. My issue sounds similar to this issue here, where some rows contain a "0" or Null value that messes with the aggregation. When I try to replicate that solution I get another confusing / unfamiliar value as well though. I've tried to fix through Magic ETL as well by replacing 0 values with Null and vice versa where applicable, applying CASE and IF statements to logic the clicks/impression calculation, etc, etc.
Overall, i'm trying to just create a very simple Click-Through-Rate (CTR) calculation using Beast Mode (i.e. total clicks divided by total impressions) but the output in the Multi-Value gauge is showing something completely different than what is shown in a table view breakdown by day. When I see it in a table view like below, its clear that the beast mode calculation is correct; it just has something to do with the Multi-Value Gauge, Date Ranges, etc.
In the image below, the "metrics_ctr" column is a default column through my data source (raw data) and the CTR-New column is my beast mode calculation (clicks/impressions) displayed as a percentage format.
My data source is aggregated/segmented by DAY with common identifiers like the above image and I use the Filter Views dashboard filtering to filter date ranges. All other metrics except for ones that require calculation/aggregation (CPC, CTR, CPCV, etc) are displaying correctly.
Any insight is appreciated! Thank you all ahead of time.
Comments
-
what's your beast mode?
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
remember zero and null are not the same. assuming your beast mode is sum(values) / count(values)
if you had 5 rows
0,0, null, 2 and 6, the average is 8/4 = 2
if you had 5 rows
null, null, null, 2 and 6 the average is 8 / 2 = 4.
if you are doing division within the row avg( colA / colB ) as opposed to sum(colA) / sum(colB) you'll get a different result too.
i
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"2 -
It seems like nulls are causing the issue (as Jae mentioned - zero and null are not the same).
Handling Nulls in the ETL may be the cleanest & easiest solution - referring to this response to handle Nulls in the ETL - https://dojo.domo.com/discussion/11843/convert-null-numeric-values-to-0-in-magic-etl (see most recent update)
0 -
Very sorry for the delay in response! Thank you both for a quick response. I solved my own issue but may need to create a new topic on a new issue.
For transparency for any other future readers, my new Beast Mode fixed the issue like others suggested:
NEW: SUM(Cost-Metric) / SUM (Impression-Metric)
OLD: AVG( Cost-Metric / Impression-Metric)
My data source holds "0" values and in the columns for days where those metrics were not captured/ads were not clicked.
New issue:
I want to see the Period-over-Period change for these new metrics, so I added a date segment to the grouping on the multi-value gauge (article here). It doesn't look like DOMO can automatically use the same dataset fields for both the gauge value AND for the comparison value. It looks like I may need to build something along these lines here in Beast Mode as well to accomplish this.
Ideally im looking to just see a very basic Period-over-Period percent change for the gauge value, but the values will be dynamic based on a global date filter on the dashboard page. Does that make sense?
Thank you all for your responses/help.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive