Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
jakobjensen
Contributor II
Contributor II

Get last value within each group based on date

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

 

1 Reply
rubenmarin

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),',')