How to wrap a DATEDIFF around this?

PJG
PJG Member
in Beast Mode

As always, I probably shouldn't be allowed near Beast Mode :)

Can someone explain how to fix my code? I assume I'm not adding DATEDIFF in the right place, as I'm always breaking it. BatchTimestamp is the date the Phase was set to the respective value. I just need the number of days between the two.

CASE WHEN `Phase` = 'Received' THEN `BatchTimestamp` END
-
CASE WHEN `Phase` = 'Ready' THEN `BatchTimestamp` END

Thanks!

Answers

  • GrantSmith
    GrantSmith Coach

    When you're evaluating this beast mode it's going record by record so one part of your equation will always be null, you'd need to calculate the max BatchTimestamp date. Are you keying based off a specific identifier field in your chart?

    Something along the lines of may work depending on how you're displaying your data: 

    DATEDIFF(MAX(CASE WHEN `Phase` = 'Ready' THEN `BatchTimestamp` END), MAX(CASE WHEN `Phase` = 'Received' THEN `BatchTimestamp` END))

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • PJG
    PJG Member

    Hi @GrantSmith , thank you for your help as always. I am not following why I need to include MAX, but perhaps I left out a key piece of information in my previous post. I need the total number of days between these phase change dates for all records, and I would then set the Aggregation to be the Average. It's the Average between them that I'm ultimately looking for.

  • GrantSmith
    GrantSmith Coach

    Do you have some sample data to see what your dataset looks like?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • PJG
    PJG Member

    Hi Grant, is this helpful? Just note the header labels are a little different from my code earlier which was more of a mockup, but the two Phases I'm interested in here are the ones starting A0 and A3, and I need to know the average number of days between the dates they were set for all records that have had both set (or the total days, then I can set Aggregation to Average). Thank you so much.

    image.png

  • marcel_luthi
    marcel_luthi Coach

    How do you know which record the A0 - Request Received and the A3 - Ready for IT PB belongs to, in your example you have 2 A0 - Request Received entries, also can a record have more than one of each of these entries, and if so, which one should be used (this is why @GrantSmith suggesting wrapping the field in a MAX function), an aggregation function will always be needed since the dates are not held on separate columns of the same row, but different rows instead.

  • PJG
    PJG Member
    edited September 2023

    The table from my screenshot includes the record name also, but I can't show that here. A record appears as many times as there are Phase changes.

    It's incredibly rare (<1% of the time) that a project would have more than one transition from A0 to A3, but if it does, I would like them all included in the calculation.

Categories