How to compare the date between current and previous record

Question & Answer
,
1

I would like to check how many months a customer doesn’t make orders by using datediff. However, Quicksight doesn’t allow me to compare the current record and the previous date if I do this: datediff({current record}, lag{current record},‘MM’) because of mismatched aggregation.

2

Hi Vicky, It is not totally clear what exactly is your requirement here however based on my understanding I have created below sample. Let me know if this works for you.

  1. Created a calculation to compute Prior Order Date using Lead Function
    Prior Order Date : lead({Order Date},[{Order Date} DESC],1,[{User Id}])

  2. Compute Date Difference using the calculation from step 1
    Date Diff(In Days) : dateDiff({Prior Order Date},max({Order Date}),“DD”)

you can combine step 1 and 2 in a single calculation however when we use lag/lead function it is an aggregation and can only be used with another aggregated column. To satisfy this we will have to use either max or min of the Order Date column.

image
image730×244 36 KB

Sample Report
image

Regards,
Karthik

2 Likes
3

Hi Karthik,
Thanks for the solution! And I am wondering if I could use custom calculated field like this one to create a visual or it could only be used in a pivot table?

4

Hi Vicky, It generally depends on the calculation type and visual that you intend to use. For instance if you have a calculation that is of type Post_Agg_Filter then as part of the visual you will need to show/display columns used in the calculation and that may pose some restrictions.

Regards,
Karthik

5

That make sense. Thanks!