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

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.

Tagged:

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • marcel_luthi
    marcel_luthi Coach
    edited May 2023 Answer ✓

    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.

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • marcel_luthi
    marcel_luthi Coach
    edited May 2023 Answer ✓

    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.