How to handle failure rates (DPPM) when Event is in different month from Sales

I need to calculate failure rates using two different date columns but the Fixed by calculation doesn't work for this.
All calculations go by the date field selected on the chart, but I need a Beast Mode that ignores the default date on the chart. For instance, if I want to show units sold by Sales Date
but also show units returned during that same month, regardless of when they were sold, I don't know how to use Return Date
for the second calculation.
I need to calculate a failure rate based upon a mixed date calculation. Units returned in June divided by units sold in June.
Unit ID | Sales Date | Return Date | Reason |
---|---|---|---|
1 | 30-Jun | ||
2 | 30-Jun | ||
3 | 30-Jun | 30-Jun | Hot |
4 | 30-Jun | 30-Jun | Cold |
5 | 30-Jun | 1-Jul | Hot |
6 | 30-Jun | 1-Jul | Cold |
7 | 1-Jul | 2-Jul | Hot |
8 | 1-Jul | 2-Jul | Cold |
9 | 1-Jul | ||
10 | 1-Jul | ||
11 | 1-Jul | ||
12 | 1-Jul | ||
Sales Date | June | July | |
count (distinct `id`) | 6 | 6 | |
Units Returned (count `Reason`) | 2 | 4 | |
Failure Rate | 2/6=.33 | 4/6=.66 | |
Hot Failures | 1 | 2 | |
Cold Failures | 1 | 2 |
Best Answer
-
If I were asked to do this, I would append the data in Magic ETL and create 2 new fields:
- A date column that contains both sales and return dates
- A flag to indicate if that row is a sale or a return
Then in a beastmode you could do your calculation:
COUNT(CASE WHEN `NewFlagField` = 'Return' THEN `Reason` END) / COUNT(DISTINCT CASE WHEN `NewFlagField` = 'Sale' THEN `ID` END)
If I solved your problem, please select "yes" above
0
Answers
-
If I were asked to do this, I would append the data in Magic ETL and create 2 new fields:
- A date column that contains both sales and return dates
- A flag to indicate if that row is a sale or a return
Then in a beastmode you could do your calculation:
COUNT(CASE WHEN `NewFlagField` = 'Return' THEN `Reason` END) / COUNT(DISTINCT CASE WHEN `NewFlagField` = 'Sale' THEN `ID` END)
If I solved your problem, please select "yes" above
0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 7 Cloud Amplifier
- 9 Federated
- 3K Transform
- 113 SQL DataFlows
- 650 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 792 Beast Mode
- 78 App Studio
- 44 Variables
- 754 Automate
- 187 Apps
- 479 APIs & Domo Developer
- 71 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 407 Distribute
- 118 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive