Help with a DATEDIFF calculation to create multiple ranges to sort on

BV_Brandon
BV_Brandon Member
edited April 2023 in Cards, Dashboards, Stories

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

  • ST_-Superman-_
    ST_-Superman-_ Coach
    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.” -Superman

Answers

  • ST_-Superman-_
    ST_-Superman-_ Coach
    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.” -Superman
  • BV_Brandon
    BV_Brandon Member

    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 accounts

  • ST_-Superman-_
    ST_-Superman-_ Coach

    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.” -Superman
  • BV_Brandon
    BV_Brandon Member

    Doh!

     

    Thanks Scott. 

  • ST_-Superman-_
    ST_-Superman-_ Coach

    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.” -Superman
  • ST_-Superman-_
    ST_-Superman-_ Coach

    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.” -Superman
  • cpbwg
    cpbwg Member

    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?


    Screen Shot 2022-10-26 at 8.18.22 PM.png


  • mhouston
    mhouston Contributor

    @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?

Categories