Negative numbers being captured in date diff explore? | The place for Zendesk users to come together and share
Skip to main content
J
Jack12
February 3, 2022
Question

Negative numbers being captured in date diff explore?

  • February 3, 2022
  • 4 replies
  • 39 views

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
      Newcomer
      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
        Jack12Author
        February 3, 2022

        Perfect, thanks.

        J
        Jack12Author
        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
        Newcomer
        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 & ConditionsAccessibility statement