Qlik Community

Ask a Question

App Development

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

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
nischwa
Contributor
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
Partner
Partner

Hi there

This should work:

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

Regards,

Mauritz

sunny_talwar

Another option is to use set analysis...

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