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.
Best Answers
-
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!**2 -
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.
0
Answers
-
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!**2 -
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.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 49 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive