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

Rodney

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

ColemenWilson

If I were asked to do this, I would append the data in Magic ETL and create 2 new fields:

1. A date column that contains both sales and return dates
2. 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)

ColemenWilson

If I were asked to do this, I would append the data in Magic ETL and create 2 new fields:

1. A date column that contains both sales and return dates
2. 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)