count groups with given value

nischwa — Sat, 16 Nov 2024 20:49:42 GMT

I have the following table:

id	caseID	status	date
1	1	open	01.05.2019
2	1	working	02.05.2019
3	1	pending	03.05.2019
4	1	closed	04.05.2019
5	2	open	02.05.2019
6	2	working	04.05.2019
7	2	pending	05.05.2019
8	3	open	03.05.2019
9	3	working	09.05.2019
10	4	open	04.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 🙂

Re: count groups with given value

Mauritz_SA — Tue, 21 May 2019 11:32:13 GMT

Hi there

This should work:

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

Regards,

Mauritz

Re: count groups with given value

sunny_talwar — Tue, 21 May 2019 12:42:06 GMT

Another option is to use set analysis...

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

topic Re: count groups with given value in App Development

count groups with given value

Re: count groups with given value

Re: count groups with given value