How to calculate Last Order Date for each customer in dataset

Question & Answer
,
1

I have a dataset which contains records with columns
CustomerId, Order Date, SaleAmount

I would like to create one more field which contains last order date for each customer.

2

Hi @yogendrasgautam - Can you please create a calculated field with below logic

minOver(min({Order date}),[{CustomerID}])

Then publish it and you this field in your aggregate column.

Regards - Sanjeeb

3

I think this will give the First Order Date

4

hi @yogendrasgautam - Change to maxOver :slight_smile:

Regards - Sanjeeb

5

It gives the same date that transaction happened.
below is my use case.

CustomerId , Order Date, Amount
1234, 2023-04-11 , 100$
4567, 2023-04-12, 100$
1234, 2023-04-13, 120 $
1234, 2023-04-16, 140 $

Would like to see result as
CustomerId , Order Date, Amount, Last Transaction Date
1234, 2023-04-11 , 100$ , null
4567, 2023-04-12, 100$, null
1234, 2023-04-13, 120 $, 2023-04-11
1234, 2023-04-16, 140 $, 2023-04-16

6

Hi @yogendrasgautam - Got it, essentially you want the previous transaction date of the customer, can you please try this.

lag(min({Order date}),[{CustomerID} ASC],1,[{CustomerID}])

See the documentation below -

Regards - Sanjeeb

1 Like
7

It worked. Thank you
However, it does not support the PRE_FILTER condition.
I would like to found the Number of customer whose last order date is greater than past 30 days.

When I select range of two days, the formulas only consider records for 2 days only

8

Hi @yogendrasgautam - Is it not possible to put a filter on the calculated field and select only last 30 days data?

Hi @Max @Biswajit_1993 - Can you guys please help on this? I thought of lag should help on this case but looks like we need to put some additional intelligence.

Regards - Sanjeeb

1 Like
9

Hi @yogendrasgautam rather than trying to do a pre-filter on the Lag, we can try moving the ‘last 30 days’ filter to occur later in the pipeline. We can do this by filtering on a table calculation of you date field, which effectively turns that filter into a ‘hider’, meaning all the data will still be in the underlying query, and the ‘filter’ happens locally just on the rendered results.

Create a calculated field like this:
min({date field}, [{date field}])

Which is just a fancy way to return the exact same date value, but ‘table calc-ed’, which moves this to the end of the query pipeline.

Then add your filter on this date field (and remove your old date filter).

Does that work?

1 Like