Set Anaysis - Range Criteria

[jmonroe918]

I am trying to create an expression that will filter and count records meeting the citeria of Open (not Closed), MDR = yes, and the Age is between 15 and 25 days. My expression (below) works correctly if I use only one of the age criterias (>14 or <26) but I can't figure out how to use the range.

=Count( Distinct {<
     [Complaints.Status]-={'Closed'},
     [Complaints.MDR]={'Yes'},
     [Complaints.Age (Days)] = {'>14','<26'}
>} [Complaints.Complaint No])

How does range criteria work in set analysis?

Thanks

Jeff

[Gysbert_Wassenaar · Accepted solution]

=Count( Distinct {<

     [Complaints.Status]-={'Closed'},

     [Complaints.MDR]={'Yes'},

    [Complaints.Age (Days)] = {'>14<26'}

>} [Complaints.Complaint No])

[jmonroe918]

I was so close; didn't remove the space wehn I deleted the comma.

Thank you Gysbert!

Jeff

[jmonroe918]

Another quick one:

How do I filter for a null value?

I’m trying to count the records where the NCMR.Disposition field is null, or empty.

Thanks.

Jeff

=Count( Distinct {<[NCMR.Statuss]-={'Closed'},

[ComplaintMDR.TaskID]={'INJ-RES-A'},

[NCMR.Disposition] = {},

[NCMR.Age (Days)] = {'<30'}
>}

[NCMR.NCMR No])

[Gysbert_Wassenaar]

That can be tricky. The best solution is to replace nulls and empty values with a real value, i.e. 'N/A', in the script:

Load ...some fields..., if(len(trim(NCMR.Disposition))=0,'N/A',NCMR.Disposition) as NCMR.Disposition

From ...

The set modifier will then be a simple [NCMR.Disposition]={'N/A'}. If you can't or won't fix this in the script you can try [NCMR.Disposition] =- {'*'} or [NCMR.Disposition] = {'=len(trim(NCMR.Disposition]))=0'}