Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

nischwa
New Contributor

count groups with given value

I have the following table:

idcaseIDstatusdate
11open01.05.2019
21working02.05.2019
31pending03.05.2019
41closed04.05.2019
52open02.05.2019
62working04.05.2019
72pending05.05.2019
83open03.05.2019
93working09.05.2019
104open04.05.2019

 

and i want to count cases with status 'working' or 'pending' as the last status.

The result should be 2. It should count caseID 2 and 3.

 

I tryed alot, but nothing worked. Best result was:

Sum(
 {<status = {'working','pending'}>} Aggr(
     If(
         Max({<status = {'working','pending'}>} TOTAL <caseID> date) = Only({<status = {'working','pending'}>} date), Sum({<status = {'working','pending'}>} 1), 0
        ), caseID, date)
)

 

On a SQL Server i would do it like this:

SELECT        P.caseID, D.department
FROM            Cases AS P LEFT OUTER JOIN
                         departments AS D ON P.costunit = D.costunit
WHERE        (P.statusDate =
                             (SELECT        MAX(statusDate) AS Expr1
                               FROM            Cases
                               WHERE        (caseID = P.caseID))) AND (P.status IN ('working', 'pending'))

 

Please help 🙂

Labels (2)
2 Replies
Mauritz_SA
Contributor III

Re: count groups with given value

Hi there

This should work:

Sum(Aggr(If(Max(date)=Max({<status = {'working','pending'}>} date),1,0),caseID))

Regards,

Mauritz

Highlighted

Re: count groups with given value

Another option is to use set analysis...

=Count(DISTINCT {<caseID = {"=Max(date) = Max({<status = {'working','pending'}>} date)"}>} caseID)