Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
pooja_sn
Creator
Creator

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
4 Replies
lironbaram
Partner
Partner

hi

create a table

reportId as dimension    

count(distinct ReportDate) as expression

sunny_talwar

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
Creator III
Creator III

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
Master III
Master III

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)