Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Count Last Occurrence of Distinct ID

I am trying to show a line chart or a bar chart representing the last time an user ID is present in the database.


With the following table :

ID, Date

A, 2014

B, 2014

A, 2015

C, 2015

C, 2017

C, 2017

D, 2017

I want the values of my chart to be 1 for 2014, 1 for 2015 and 2 for 2017.

This thread Count Distinct Accumulation in Qlik Sense helped me a lot but I still don't know how to use the last occurrence instead of the first for a distinct count.

1 Solution

Accepted Solutions
Ralf_Heukäufer
Partner - Creator III
Partner - Creator III

If your date isn´t a date but only a year you must write max(Date) instead of Max(Date#(Date,'YYYY'))

aggr works like a group by in the script in your case you must group the max date by ID.

View solution in original post

6 Replies
agigliotti
Partner - Champion
Partner - Champion

could you post a sample app with scramble data to help understanding what are you trying to reach out ?

shraddha_g
Partner - Master III
Partner - Master III

in Script,

load ID,

MAx(Date#(Date,'YYYY')) as Date

From Table

Group by ID

And in Barchart or line chart

Dimension : Date

Measure - Count(Distinct ID)

Ralf_Heukäufer
Partner - Creator III
Partner - Creator III

Hello Manuel,

try aggr(max(Date),ID) as expression and ID as dimension.

Anonymous
Not applicable
Author

It seems like Max(Date#(Date,'YYYY')) isn't a valid dimension.

I'm trying to understand how Aggr works, I have trouble explaining exactly what I want.

Ralf_Heukäufer
Partner - Creator III
Partner - Creator III

If your date isn´t a date but only a year you must write max(Date) instead of Max(Date#(Date,'YYYY'))

aggr works like a group by in the script in your case you must group the max date by ID.

Anonymous
Not applicable
Author

It works, thank you for your answer