Get last value within each group based on date

jakobjensen — Fri, 07 Jan 2022 08:33:31 GMT

I have the following table

user_id level date 1 0 2021-01-01 1 1 2021-01-15 1 0 2021-02-01 2 1 2021-01-01 2 2 2021-02-17 3 2 2021-03-15 3 0 2021-04-13 4 1 2021-01-01

note the dates correspond to a change in the level i.e the last "level" recorded is their current level. I want to calculate how many there's in each level (theres level 0,1,2 and 3) i.e I need for each "user_id" get the last "level" value recorded which in the example above would be

level count 0 2 #user_id=1,3 1 1 #user_id=4 2 1 #user_id=2

Re: Get last value within each group based on date

rubenmarin — Fri, 07 Jan 2022 12:38:14 GMT

Hi, for the count you can use:

Sum(Aggr(If(date=max(TOTAL <user_id> date), 1,0),level,date,user_id))

For the text with users id's:

='#user_id=' & Concat(DISTINCT Aggr(If(date=max(TOTAL <user_id> date), user_id),level,date,user_id),',')

topic Get last value within each group based on date in App Development

Get last value within each group based on date

Re: Get last value within each group based on date