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: 
Not applicable

Expression with latest row count


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.

NamerangeTime
Anthony<31 Days08/31/15
Ethan<31 Days08/13/15
Ethan<31 Days08/13/15
Ethan31-60 Days07/13/15
Ethan31-60 Days07/13/15
Ethan31-60 Days07/13/15
Ellon31-60 Days08/03/15
Ellon31-60 Days08/03/15
Ellon31-60 Days08/03/15
Ellon31-60 Days08/04/15
karn<31 Days08/27/15
Venasa<31 Days08/25/15
Sauber<31 Days08/28/15
Sauber31-60 Days07/16/15
Rafael31-60 Days07/20/15
stuart31-60 Days07/29/15
stuart31-60 Days08/11/15
stuart31-60 Days08/11/15
Wallen<31 Days09/03/15
Wallen<31 Days09/03/15
dave31-60 Days08/07/15
lee<31 Days08/16/15
lee31-60 Days07/26/15
James31-60 Days07/16/15
James31-60 Days07/23/15
laura31-60 Days07/23/15
laura31-60 Days07/29/15
Ethan61-90 Days06/16/15

So basically the expression would result in the below table

rangecount
<31 Days7
31-60 Days6
61-90 Days0

Thanks for the help  !

1 Solution

Accepted Solutions
maxgro
MVP
MVP

RESULT


1.png


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;

View solution in original post

7 Replies
dclark0699
Creator
Creator

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

sudeepkm
Specialist III
Specialist III

Please find the example attached. Let me know if you are not able to pen the qvw.

dclark0699
Creator
Creator

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.

maxgro
MVP
MVP

RESULT


1.png


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;

dclark0699
Creator
Creator

Didn't know this was possible:


FROM

[https://community.qlik.com/thread/180220]

(html, codepage is 1252, embedded labels, table is @1);

Very cool

Anonymous
Not applicable
Author

hi sanket,

may be use:

=Count(if(aggr(max(Time),Name), Name))

maxgro
MVP
MVP

and also it's simple and quick, just copy the url, click the web files button and paste