Caculate percentage in same month when using two date columns in same data table

Rodney

Hello Domo Community,

I am trying to count the number of distinct product id’s that had a field issue during a month and compare that with the count of sales units in that same month. The data looks like this:

Unit id	Sales Date	Service Date
1	1-Jan
2	2-Jan	2-Feb
3	1-Feb
4	2-Feb	2-Apr
5	1-Mar
6	2-Mar
7	1-Apr
8	2-Apr	2-Apr

I want to determine a ratio of service events vs sales units for each month, based on the service event date. The output might look like this:

Month	Count of Sales Units	Count of Service Units	Service Units / Sales Units
Jan	2	0	0.0
Feb	2	1	0.5
Mar	2	0	0.0
April	2	2	1.0

Because one row may have a date in two columns, Domo will force everything to use one column or the other, giving me the wrong results. I need service units from April compared with Sales units from April, etc. and don’t know how to make Domo calculate this. Please help.

GrantSmith

You should restructure your data so that it's stacked so you have one row per observation (sales or service)

Unit ID | Date | Date Type

1 | 1-Jan | Sales

2 | 2-Jan | Sales

2 | 2-Feb | Service

Then use some beast modes to count your sales and service units:

COUNT(CASE WHEN `Date Type` = 'Sales' THEN `Unit ID` ID END)

COUNT(CASE WHEN `Date Type` = 'Service' THEN `Unit ID` ID END)

COUNT(CASE WHEN `Date Type` = 'Service' THEN `Unit ID` ID END)
/

COUNT(CASE WHEN `Date Type` = 'Sales' THEN `Unit ID` ID END)

This way everything is on the same date field.

marcel_luthi

Just in case needed, here's what the ETL to stack this might end up looking like:

From there you use the formulas that @GrantSmith shared, the only thing you might want to consider is on the last one if it's possible to have dates with Services but no Sales as this would make the ratio to be a DIV/0, which you might want to handle in a specific way for your company.

GrantSmith

You should restructure your data so that it's stacked so you have one row per observation (sales or service)

Unit ID | Date | Date Type

1 | 1-Jan | Sales

2 | 2-Jan | Sales

2 | 2-Feb | Service

Then use some beast modes to count your sales and service units:

COUNT(CASE WHEN `Date Type` = 'Sales' THEN `Unit ID` ID END)

COUNT(CASE WHEN `Date Type` = 'Service' THEN `Unit ID` ID END)

COUNT(CASE WHEN `Date Type` = 'Service' THEN `Unit ID` ID END)
/

COUNT(CASE WHEN `Date Type` = 'Sales' THEN `Unit ID` ID END)

This way everything is on the same date field.

marcel_luthi

Just in case needed, here's what the ETL to stack this might end up looking like:

From there you use the formulas that @GrantSmith shared, the only thing you might want to consider is on the last one if it's possible to have dates with Services but no Sales as this would make the ratio to be a DIV/0, which you might want to handle in a specific way for your company.