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.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 282 Workbench
- 3 Cloud Amplifier
- 4 Federated
- 2.8K Transform
- 86 SQL DataFlows
- 548 Datasets
- 2.2K Magic ETL
- 3.2K Visualize
- 2.3K Charting
- 544 Beast Mode
- App Studio
- 26 Variables
- 566 Automate
- 134 Apps
- 411 APIs & Domo Developer
- 21 Workflows
- DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 345 Distribute
- 87 Domo Everywhere
- 257 Scheduled Reports
- 1 Software Integrations
- 85 Manage
- 84 Governance & Security
- 8 Product Release Questions
- Community Forums
- 41 Getting Started
- 27 Community Member Introductions
- 81 Community Announcements
- 4.8K Archive