topic Re: Expression with latest row count in QlikView

Expression with latest row count

Fri, 11 Sep 2015 17:01:07 GMT

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 !

Re: Expression with latest row count

dclark0699 — Fri, 11 Sep 2015 17:08:15 GMT

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

Re: Expression with latest row count

sudeepkm — Fri, 11 Sep 2015 17:22:43 GMT

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

Re: Expression with latest row count

dclark0699 — Fri, 11 Sep 2015 17:28:44 GMT

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.

Re: Expression with latest row count

maxgro — Fri, 11 Sep 2015 17:29:42 GMT

RESULT

SCRIPT

SET DateFormat='MM/DD/YYYY';

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;

Re: Expression with latest row count

dclark0699 — Fri, 11 Sep 2015 17:36:56 GMT

Didn't know this was possible:

FROM

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

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

Very cool

Re: Expression with latest row count

Anonymous — Fri, 11 Sep 2015 17:38:01 GMT

hi sanket,

may be use:

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

Re: Expression with latest row count

maxgro — Fri, 11 Sep 2015 17:40:45 GMT

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