Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi All,
i am trying to create a bar chart with dimension as range from the below dataset and an expression which would count distinct Names but only for the latest row.
Ex. if Ethan has a latest date tagged as 8/31/2015 then he should be counted only once and in the range of <31 days and not in 31-60 days or 61-90 days.
Name | range | Time |
Anthony | <31 Days | 08/31/15 |
Ethan | <31 Days | 08/13/15 |
Ethan | <31 Days | 08/13/15 |
Ethan | 31-60 Days | 07/13/15 |
Ethan | 31-60 Days | 07/13/15 |
Ethan | 31-60 Days | 07/13/15 |
Ellon | 31-60 Days | 08/03/15 |
Ellon | 31-60 Days | 08/03/15 |
Ellon | 31-60 Days | 08/03/15 |
Ellon | 31-60 Days | 08/04/15 |
karn | <31 Days | 08/27/15 |
Venasa | <31 Days | 08/25/15 |
Sauber | <31 Days | 08/28/15 |
Sauber | 31-60 Days | 07/16/15 |
Rafael | 31-60 Days | 07/20/15 |
stuart | 31-60 Days | 07/29/15 |
stuart | 31-60 Days | 08/11/15 |
stuart | 31-60 Days | 08/11/15 |
Wallen | <31 Days | 09/03/15 |
Wallen | <31 Days | 09/03/15 |
dave | 31-60 Days | 08/07/15 |
lee | <31 Days | 08/16/15 |
lee | 31-60 Days | 07/26/15 |
James | 31-60 Days | 07/16/15 |
James | 31-60 Days | 07/23/15 |
laura | 31-60 Days | 07/23/15 |
laura | 31-60 Days | 07/29/15 |
Ethan | 61-90 Days | 06/16/15 |
So basically the expression would result in the below table
range | count |
<31 Days | 7 |
31-60 Days | 6 |
61-90 Days | 0 |
Thanks for the help !
RESULT
SCRIPT
SET DateFormat='MM/DD/YYYY';
z:
LOAD
rowno() as id,
Name,
range,
Time
FROM
[https://community.qlik.com/thread/180220]
(html, codepage is 1252, embedded labels, table is @1);
left join (z)
load
Name,
date(Max(Time)) as Time2
Resident z
Group By Name;
left join (z)
LOAD id, if(Time=Time2,1,0) as MaxTime
Resident z;
I'd recommend creating a flag in the script for each person's latest date and then using that in set analysis, it would end up being much more straightforward than the likely nested aggregations it would require to do it on the fly in the UI
Please find the example attached. Let me know if you are not able to pen the qvw.
While this functionally achieves the desired result, it also removes all the rest of the data from the application.
I'd encourage a LEFT JOIN with a flag and then set analysis as in my version of the attachment. This way all the data remains in the app.
RESULT
SCRIPT
SET DateFormat='MM/DD/YYYY';
z:
LOAD
rowno() as id,
Name,
range,
Time
FROM
[https://community.qlik.com/thread/180220]
(html, codepage is 1252, embedded labels, table is @1);
left join (z)
load
Name,
date(Max(Time)) as Time2
Resident z
Group By Name;
left join (z)
LOAD id, if(Time=Time2,1,0) as MaxTime
Resident z;
Didn't know this was possible:
FROM
[https://community.qlik.com/thread/180220]
(html, codepage is 1252, embedded labels, table is @1);
Very cool
hi sanket,
may be use:
=Count(if(aggr(max(Time),Name), Name))
and also it's simple and quick, just copy the url, click the web files button and paste