topic Re: Get last date for each ID in Connectivity & Data Prep

Get last date for each ID

jakobjensen — Thu, 06 Jan 2022 07:55:53 GMT

Say I have the following:

#[transactions] id date --+----- 1 2021-01-01 1 2021-01-05 2 2021-01-01 2 2021-02-03

and I want to get the day since last purchase. If I create a measure like this

Now()-date

I get it for all transactions but I just want it for the last transaction for each ID.

I could do something like "MIN(Now()-date)" but I don't know how to calculate it within a "group" i.e within each "id"

anat — Thu, 06 Jan 2022 08:25:06 GMT

load id,min(date) resident table group by id;

you will get min date for each id

then u can do the calculation on top..

rubenmarin — Thu, 06 Jan 2022 08:26:35 GMT

Hi, you can use an expression like:

if(date=Max(TOTAL <id> date),date-Max(TOTAL <id> date, 2))

or "if(date=Max({1} TOTAL <id> date),date-Max({1} TOTAL <id> date, 2))" to ignore date selections