Counting Distinct Cases with a Set Analysis of Counting differing fields less than or equal to

[clairep1962]

Hi,

Firstly thanks for all the great help and support in this group.

CaseID	WO	WO_LI	WorkDate
0113502199	WO-00788684	WL-02394738	16.01.2020
0114682262	WO-01172008	WL-02394775	14.01.2020
0114682262	WO-01172008	WL-02394776	13.01.2020
0114704373	WO-01180372	WL-02320567	06.01.2020
0114704373	WO-01180372	WL-02320568	06.01.2020
0114755958	WO-01197485	WL-02375006	15.01.2020
0114755958	WO-01197485	WL-02375008	15.01.2020
0114757407	WO-01198052	WL-02404118	19.01.2020
0114775610	WO-01207778	WL-02394789	15.01.2020
0114825110	WO-01229772	WL-02419773	22.01.2020
0114825110	WO-01230882	WL-02426943	23.01.2020
0114825110	WO-01230963	WL-02422718	23.01.2020
0114825110	WO-01230963	WL-02426411	23.01.2020

Problem Domain

I have Cases with have 1 to many Work orders which in turn have 1 to Many Work Order Lines

There may be multiple engineer visits on a Work Order Line. The challenge is to count all Distinct Cases where there have been one or less days of work. 

The above data would thus resolve to 7 Distinct Cases and 5 of them where 1 or less work days are evident. So Case  0114682262 would not be counted as work took place over 2 days, the 13th and 14th but  0114704373 would be counted as all work was completed in one calendar day.

So how do I write an expression with Set Analysis to count distinct cases for 1 or less work days.

The solution will be used in a Straight Table and a Text field.

Many Thanks

Claire

[sunny_talwar · Accepted solution]

Try this

Count(DISTINCT {<CaseID = {"=Count(DISTINCT WorkDate) = 1"}>} CaseID)

[clairep1962]

Thank you that worked perfectly. Hope you are safe and well in these challenging times.

Claire x