Negative numbers being captured in date diff explore? | Community
Skip to main content

Negative numbers being captured in date diff explore?

  • February 3, 2022
  • 4 replies
  • 0 views
J

Hey guys, 

So I've created a date diff query for capture the amount of days between a custom attribute which is the ticket created date vs their installation date: 

IF (DATE_DIFF([Ticket created - Date],[Install date - Date],"nb_of_days")>30) THEN FALSE ELSE TRUE ENDIF

It works well but it is also capturing negative numbers on the true part of the query when I drill in.

Any ideas on how I can fix this?

      4 replies

      Zendesk13
      • Zendesk13
      • February 3, 2022

      Hi @jack12, that is happening because the install date is greater than the ticket creation date for those cases, and because negative values are less than 30, the formula returns TRUE.

      If you want to exclude these cases (i.e. considering negative values as FALSE), I'd suggest modifying the formula to:

      IF (DATE_DIFF([Ticket created - Date],[Install date - Date],"nb_of_days")>30)
      OR ([Install date - Date] > [Ticket created - Date]) 
      THEN FALSE 
      ELSE TRUE 
      ENDIF

      Hope this helps!

        J
        • Jack12
        • Author
        • February 3, 2022

        Perfect, thanks.

        J
        • Jack12
        • Author
        • February 3, 2022

        One slight error, you have placed 2 brackets on the end of the second line, when removing one works perfect.

        Zendesk13
        • Zendesk13
        • February 3, 2022

        Hi Jack, I'm glad it worked! And good catch on that extra bracket, thanks for the heads up (corrected it in the previous comment).

        Terms & ConditionsCookie settingsAccessibility statement