Help with a DATEDIFF calculation to create multiple ranges to sort on
Hi, i'm trying to create a card to segment the "Time to Spend" for new accounts. Check the calc below, I hope it is self explanatory. It almost works! End result is that it only creates 2 different segments. I get either 'Same Day' or 'Spent month or more later'. No range inbetween gets defined. Do I need to nest multiple case statements or do I just have the syntax off somewhere? Can I use AND that way?
Thanks in advance!
CASE
WHEN DATEDIFF(`created_at`,`first_invoiced`) = 0 THEN 'Same Day'
WHEN DATEDIFF(`created_at`,`first_invoiced`) = 1 THEN 'Next Day'
WHEN DATEDIFF(`created_at`,`first_invoiced`) >= 2 AND DATEDIFF(`created_at`,`first_invoiced`) <= 3 THEN '2-3 Days'
WHEN DATEDIFF(`created_at`,`first_invoiced`) >= 4 AND DATEDIFF(`created_at`,`first_invoiced`) <= 7 THEN 'One Week'
WHEN DATEDIFF(`created_at`,`first_invoiced`) >= 8 AND DATEDIFF(`created_at`,`first_invoiced`) <= 14 THEN '2 Weeks'
WHEN DATEDIFF(`created_at`,`first_invoiced`) >= 14 AND DATEDIFF(`created_at`,`first_invoiced`) <= 31 THEN '2-4 Weeks'
ELSE 'Spent month or more later'
END
Best Answer
-
try flipping the values your are looking for to negatives
DATEDIFF takes the first date minus the second date. If I created the account yesterday and bought today, 4/25/2018 - 4/26/2018 would give you -1; not 1.
Either switch the sign, or change the order of the dates, or take an ABS() of the calculation
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1
Answers
-
try flipping the values your are looking for to negatives
DATEDIFF takes the first date minus the second date. If I created the account yesterday and bought today, 4/25/2018 - 4/26/2018 would give you -1; not 1.
Either switch the sign, or change the order of the dates, or take an ABS() of the calculation
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
I'm trying other options while waiting for someone to reply. I stepped back to try and address it in the original SQL query but now i'm running into syntax issues. I'm still getting comfortable with SQL and i'm struggling to see where this query goes wrong. I'm guessing that creating the new column with the calculation already made will simplify the beast mode calc. Though I imagine its just two ways of getting to the same place. Is that right?
SELECT
`created_at`,
`first_invoiced`,
`last_invoiced`,
`payment_terms`,
`pcode`,
`status`,
`total_invoices`,
`total_revenue_usd`
DATEDIFF(`created_at`,`first_invoiced`) AS `Time to Spend`
FROM accounts0 -
no. You just missed a comma after `total_revenue_usd`
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
Doh!
Thanks Scott.
0 -
No worries @BV_Brandon. I was in your shoes a year ago, new to SQL. Take full advantage of the Dojo and don't be afraid to ask questions.
The Domo support number was also a great resource while I was getting up to speed.
801.805.9505
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
One more bit of advice on this Case Statement. The case statement runs through the when statements until it finds one that is true and then it stops. You can simplify this statement a bit if you understand that:
CASE
WHEN DATEDIFF(`created_at`,`first_invoiced`) = 0 THEN 'Same Day'
WHEN DATEDIFF(`created_at`,`first_invoiced`) = -1 THEN 'Next Day'
WHEN DATEDIFF(`created_at`,`first_invoiced`) >= -3 THEN '2-3 Days'
WHEN DATEDIFF(`created_at`,`first_invoiced`) >= -7 THEN 'One Week'
WHEN DATEDIFF(`created_at`,`first_invoiced`) >= -13 THEN '2 Weeks'
WHEN DATEDIFF(`created_at`,`first_invoiced`) >= -30 THEN '2-4 Weeks'
ELSE 'Spent month or more later'
END
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
When we do DATEFIFF, it yields a "blue" dimension beast mode calculation and not a "green" Measure calculation. This is problematic because we want to do alerts based on the measure, but it doesn't offer the ability to alert (or do a summary number) because it's not a measure. Seems like a bug? Anyone know a workaround to get the days between two dates as a measure?
0 -
@cpbwg i think you can force it to numeric by multiplying by 1 as a workaround. I tested this in my own instance and the date_diff() function gave me a measure type field without doing this workaround. Have you confirmed you don't have any unexpected values in your dataset? Are your date fields actually date types?
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive