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

hi

build a table with

reportid as dimension    

count(distinct ReportDate) as expression

tresesco
MVP
MVP

Try :

Dim: ReportId

Exp: Count(if(ReportDate=Aggr(NODISTINCT Max(ReportDate), ReportId), ReportDate))

YoussefBelloum
Champion
Champion

Hi,

A third "script" solution would be this:

reportid:

LOAD ReportId, ReportDate, RowNo() as key;

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(reportid)

LOAD

ReportId, Date(Max(ReportDate)) as MaxReportDate

Resident reportid

Group by ReportId;

final:

LOAD *, if(ReportDate=MaxReportDate, 1,0) as flag_max

Resident reportid;

DROP Table reportid;


PFA

vishalarote
Partner
Partner

Table:

load *,recno() as Flag,

Date(Date#(ReportDate,'DD/MM/YYYY'),'DD/MM/YYYY') as date 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

];

drop Field ReportDate;

left join(Table)

t1:

load ReportId,

date(Max(date),'DD/MM/YYYY') as V

Resident Table

Group by ReportId;

left join(Table)

load *,

if(date=V,1,0) as v2

Resident Table;

t2:

load ReportId,

Count(v2) as Volume

Resident Table

where v2=1

Group by ReportId;

Capture1234.PNG

pooja_sn
Creator
Creator
Author

Cool. and How do i modify this expression to add some more conditions if i want this pivot for Region=MA only

tresesco
MVP
MVP

Try like:

Count( {<Region={'MA'}>} if(ReportDate=Aggr(NODISTINCT Max({<Region={'MA'}>} ReportDate), ReportId), ReportDate))