How to find the difference between the current row date value with the next row date value

Question & Answer
, , , ,
1

Hi,

I am trying to find the difference between the date values which stored in the same field. I want to find the span of consecutive calendar days. I tried with window functions like difference, lag, rank and ended up with syntax error. Tried using the similar functions in custom sql as well and did not work. Please advise. Thanks !

For example, the date field in the order table have the below values and I have to find the day delta in QS.
Date Find the Day Delta/Date Difference

1/1/2023 - 1 (1/2/2023 - 1/1/2023)
1/2/2023 - 0 (1/2/2023 - 1/2/2023)
1/2/2023 - 0 (1/2/2023 - 1/2/2023)
1/2/2023 - 1 (1/3/2023 - 1/2/2023)
1/3/2023 - 4 (1/7/2023 - 1/3/2023)
1/7/2023 - 5 (1/12/2023 - 1/7/2023)
1/12/2023 - 3 (1/15/2023 - 1/12/2023)
1/15/2023 - 0 (1/15/2023 - 1/15/2023)
1/15/2023 - 0 (1/15/2023 - 1/15/2023)
1/15/2023 - 1 (1/16/2023 - 1/15/2023)
1/16/2023

2

Hi @Saral - If 1/1/2023 - 1 (1/2/2023 - 1/1/2023) is one of the values in what you call “Date field” then it’s almost certain it’s not a date field in QuickSight and is rather a varchar/string.

First you will have to create a proper date field to hold the order date and then you can look into using window functions to get the delta.

If you do have a proper date field please post some dummy data in csv format and I can take a look in my environment.

1 Like
3

Hi @eperts - Thanks for your response. The data type of the date field is the timestamp.

4

Hi @Saral

Have you tried using dateDiff?

5

Hi @Max ,

Yes, the issue fixed. Thanks!