Difference between 2 rows

Hi all,
I want to calculate difference between 2 rows of a date field. Is that possible in Quicksight?

Hi @dipanshugarg

Thank you for your message.

Please find below information on how to calculate difference between two rows on a date field.

Regards,

Demola

1 Like

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

Hi @dipanshugarg

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!