Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
pooja_sn
Contributor

count of maxDates only

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:

  

ReportIdReportDate
120/01/2018
122/01/2018
102/03/2018
428/08/2018
405/12/2018
515/07/2018
621/09/2018
102/03/2018
102/03/2018
102/03/2018
405/12/2018
405/12/2018
405/12/2018

Output:

  

ReportIdVolume
14
43
51
61
Tags (1)
4 Replies
Partner
Partner

Re: count of maxDates only

hi

create a table

reportId as dimension    

count(distinct ReportDate) as expression

Re: count of maxDates only

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

120/01/2018
122/01/2018
102/03/2018
102/03/2018
102/03/2018
102/03/2018

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

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

big_dreams
Contributor III

Re: count of maxDates only

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

1, 02/03/2018

1, 02/03/2018

4, 05/12/2018

4, 05/12/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

sasiparupudi1
Honored Contributor III

Re: count of maxDates only

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

1,02/03/2018

1,02/03/2018

4,05/12/2018

4,05/12/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)