calculating the 90th percentile of a data set which has nulls

[#1]

Hi, I am creating a dashboard for waiting times in the NHS. The data is by specialty and weeks waited, when working out the 90th percentile it doesnt calculate correctly, as for example, there could be 10 patients wiating 9 weeks, then none waiting 10 weeks or 11 weeks, then 4 waiting 12 weeks... the calculation looks at this and skips out weeks 10 and 11 (treats it as a null rather than a number 0).

is there a way to get over this. we need something like if null then 0 within the data?

Thanks

[sunny_talwar]

You can replace the nulls with 0 within the script. You just need to do something like this:

LOAD yourOtherFieldNames,

          If(Len(Trim(PatientWaiting)) = 0, 0, PatientWaiting) as PatientWaiting

Resident/FROM xyz;

This should bring your nulls into your fractile calculation.

HTH

Best,

Sunny

[sunny_talwar]

You can also refer to the following for more details on the topic of null: NULL handling in QlikView

Best,

Sunny

[#4]

Thank you. Can I do it using a sum too. The dashboard works by summing a metric value from a table which happens to be patients in this particular part. Something like the below….? If the sum of the metric value is null then 0 – when I show it in a graph or pivot by weeks waited.

// If(Len(Trim(sum("Metric_Value"))) = 0, 0, Metric_Value) as PatientWaiting

Hope that makes sense

Kate Hunter

Senior Information Analyst

Planned Care Team

Derby Hospitals NHS Foundation Trust

Level 5

Tel: 01332 786745

Ext: 86745

[sunny_talwar]

You can but anytime you use aggregate functions (Count, Min, Max, Sum) within your LOAD statement you need to use a group by statement, else you will get an error while running your script. So be careful when you do your grouping in the script.

HTH

Best,

Sunny