Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tertre_m
New Contributor II

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
Contributor III

Re: Count Last Occurrence of Distinct ID

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.

6 Replies
agigliotti
Honored Contributor II

Re: Count Last Occurrence of Distinct ID

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

shraddha_g
Honored Contributor III

Re: Count Last Occurrence of Distinct ID

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
Contributor III

Re: Count Last Occurrence of Distinct ID

Hello Manuel,

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

tertre_m
New Contributor II

Re: Count Last Occurrence of Distinct ID

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
Contributor III

Re: Count Last Occurrence of Distinct ID

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.

tertre_m
New Contributor II

Re: Count Last Occurrence of Distinct ID

It works, thank you for your answer

Community Browser