topic Re: Set Anaysis - Range Criteria in QlikView

Set Anaysis - Range Criteria

jmonroe918 — Sun, 02 Mar 2014 19:22:26 GMT

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

Re: Set Anaysis - Range Criteria

Gysbert_Wassenaar — Sun, 02 Mar 2014 20:13:44 GMT

=Count( Distinct {<

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

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

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

>} [Complaints.Complaint No])

Re: Set Anaysis - Range Criteria

jmonroe918 — Sun, 02 Mar 2014 20:19:51 GMT

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

Thank you Gysbert!

Jeff

Re: Set Anaysis - Range Criteria

jmonroe918 — Sun, 02 Mar 2014 20:53:03 GMT

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])

Re: Set Anaysis - Range Criteria

Gysbert_Wassenaar — Sun, 02 Mar 2014 21:11:11 GMT

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'}