Hi all,
I want to calculate difference between 2 rows of a date field. Is that possible in Quicksight?
Thank you for your message.
Please find below information on how to calculate difference between two rows on a date field.
Regards,
Demola
Hi Demola,
Thank you for your reply. I tried this function but it only works with columns. For example, I want to know the name of a customer who has more than 100 days between 2 purchase dates. How will I do that when there is only field for purchase date.
Thanks
Dipanshu
Have you tried using a lag function and dateDiff in concert?
Hi @Max
I tried using the lag function with customer’s email address as the sort column. But even when the date column is null , I am getting some values in the lag column. So I am not sure if the lag function is working correctly here.
Hello @dipanshugarg , lag function itself won’t do the date difference, lag will return you the nth (first - 1 in this case) previous value for the selected measure based on your sort and partitions.
e.g. for a dataset like this
Name | Number of customers | Customers update date |
---|---|---|
Peter | 20 | 10-09-2022 |
Joe | 15 | 23-06-2021 |
John | 12 | 21-01-2021 |
Peter | 15 | 20-12-2020 |
Joe | 10 | 20-10-2020 |
John | 10 | 21-01-2020 |
If we create a calculated field (lag_calc) using lag function as follows:
lag(sum(number_of_customers), [customers_update_date ASC], 1, [name])
We wil get a table like this:
Name | Number of customers | Customers update date | lag_calc |
---|---|---|---|
Peter | 20 | 10-09-2022 | 15 |
Joe | 15 | 23-06-2021 | 10 |
John | 12 | 21-01-2021 | 10 |
Peter | 15 | 20-12-2020 | |
Joe | 10 | 20-10-2020 | |
John | 10 | 21-01-2020 |
This means that you will only get the nth previous measure based on the sort and grouping defined, if you want to calculate the difference (of customers in this case) you will need to do something like this:
customer_increase:
sum(number_of_customers) - lag(sum(number_of_customers), [customers_update_date ASC], 1, [name])
To get something like this
Name | Number of customers | Customers update date | lag_calc | customer_increase |
---|---|---|---|---|
Peter | 20 | 10-09-2022 | 15 | 5 |
Joe | 15 | 23-06-2021 | 10 | 5 |
John | 12 | 21-01-2021 | 10 | 2 |
Peter | 15 | 20-12-2020 | ||
Joe | 10 | 20-10-2020 | ||
John | 10 | 21-01-2020 |
So in your case as you want to calculate a date difference you will need to use lag in combination with dateDiff to calculate the difference between the current row date and the nth previous row date (resulting from the lag function)
Hope it helps, I am marking this question as solved, if that’s not the case the case please let us know.
Thanks for posting your questions on the QuickSight Community Q&A Forum!