topic Re: count of maxDates only in QlikView

count of maxDates only

pooja_sn — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have this raw data in qlikview having multiple ReportIds & each Id having ReportDates. I need to show a pivot displaying count of ReportId's having maximum ReportDate only for that particular report.

Input data:

ReportId	ReportDate
1	20/01/2018
1	22/01/2018
1	02/03/2018
4	28/08/2018
4	05/12/2018
5	15/07/2018
6	21/09/2018
1	02/03/2018
1	02/03/2018
1	02/03/2018
4	05/12/2018
4	05/12/2018
4	05/12/2018

Output:

ReportId	Volume
1	4
4	3
5	1
6	1

Re: count of maxDates only

lironbaram — Tue, 15 May 2018 08:33:33 GMT

create a table

reportId as dimension

count(distinct ReportDate) as expression

Re: count of maxDates only

sunny_talwar — Tue, 15 May 2018 11:20:39 GMT

I don't think this would work... because ReportId = 1 has only 3 distinct dates

1	20/01/2018
1	22/01/2018
1	02/03/2018

1	02/03/2018
1	02/03/2018
1	02/03/2018

Similarly, ReportId = 4 has only 2 distinct dates....

Not sure I understand the logic... can you please elaborate pooja_sn

Re: count of maxDates only

big_dreams — Tue, 15 May 2018 11:52:56 GMT

Hi,

Looking for below solution

in back end modify your script like

Test:

Load ReportId,date(Date#(ReportDate,'DD/MM/YYYY')) as ReportDate INLINE [

ReportId, ReportDate

1, 20/01/2018

1, 22/01/2018

1, 02/03/2018

4, 28/08/2018

4, 05/12/2018

5, 15/07/2018

6, 21/09/2018

1, 02/03/2018

4, 05/12/2018

];

Left join

Load ReportId,Date(max(ReportDate)) as maxDate Resident Test Group by ReportId;

Final:

Load ReportId,maxDate,ReportDate,if(ReportDate=maxDate,1,0) as flag Resident Test;

drop table Test;

In front end take straight table

dimension : ReportId

Expression: sum(flag)

Regards

Re: count of maxDates only

sasiparupudi1 — Tue, 15 May 2018 11:53:17 GMT

May be try

T1:

Load * Inline

[

ReportId,ReportDate

1,20/01/2018

1,22/01/2018

1,02/03/2018

4,28/08/2018

4,05/12/2018

5,15/07/2018

6,21/09/2018

1,02/03/2018

4,05/12/2018

];

Left Join(T1)

Load

ReportId,

Max(ReportDate) as ReportDate,

1 as MaxReportDate

Resident T1

Group By

ReportId

;

in the front end, user reportid as dimension

with expression

count({<MaxReportDate={1}>} ReportId)