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

Re: count of maxDates only

hi

build a table with

reportid as dimension    

count(distinct ReportDate) as expression

MVP
MVP

Re: count of maxDates only

Try :

Dim: ReportId

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

YoussefBelloum
Esteemed Contributor

Re: count of maxDates only

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

Partner
Partner

Re: count of maxDates only

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
Contributor

Re: count of maxDates only

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

MVP
MVP

Re: count of maxDates only

Try like:

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