Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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 - Master III
Partner - Master III

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 - Creator II
Partner - Creator II

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