Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
could you post a sample app with scramble data to help understanding what are you trying to reach out ?
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)
Hello Manuel,
try aggr(max(Date),ID) as expression and ID as dimension.
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.
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.
It works, thank you for your answer