4 Replies Latest reply: Oct 16, 2012 11:48 AM by Jonathan Dienst

# How to calculate the Median of 2 values concatenated together

Hello

Could someone kindly help me write the correct expression to find the median of 2 values added together

=

Median(Count([Time to Treatment])+Count(TREATMENT_WAIT_MINS

))

Time to Treatment and Treatment Wait Times are wait times but from 2 different departments.  However I don't think that I want to count them. I just want them concatendated

Kind Regards

Helen

• ###### Re: How to calculate the Median of 2 values concatenated together

Dear Helen

Try the following expression:

(Median(aggr(Only([Time to Treatment]),[Time to Treatment]))) +

(Median(aggr(Only (TREATMENT_WAIT_MINS),TREATMENT_WAIT_MINS)))

Regards

Rahul

• ###### Re: How to calculate the Median of 2 values concatenated together

Helen

Assuming that you want a single median of the two fields together, how about:

=RangeFractile(.5, \$(=Concat([Time to Treatment], ',') & ',' & Concat(TREATMENT_WAIT_MINS, ',')))

(Using 50% Fractile which is the same as median because there is no RangeMedian function)

Hope that helps

Jonathan

• ###### Re: How to calculate the Median of 2 values concatenated together

Hello Jonathan

Thanks for this, it does exactly what I wanted

However, as I am new to Qlikview how would I write, using the formula you have posted above to include and if statement

As follows:

Time

I have tried writing something like this, using your technique, but it doesn't seem to work yet

+

Time(RangeFractile(.95, {<

[Patient Admitted?]={"1"}>} \$(=Concat([Total Wait], ',') & ',' & Concat {<[ADMISSION FLAG]={"Y"}>}(CONCLUSION_WAIT_MINS, ','))

Any help or advice would be greatly appreciated

Thanks

Helen

• ###### Re: How to calculate the Median of 2 values concatenated together

Helen

I do not really understand your last question.

Just to expand on the earlier posts:

• Fractile(...) is a field aggregation function and computes the fractile for a field or field expression.
• RangeFractile(...) is a function that accepts a range of numbers and computes the fractile of these numbers. RangeFractile does not accept set expressions.
• A 50% fractile is the same as the median.

Taking your second expression, I have (I hope) corrected the syntax:

=RangeFractile(.95, \$(=Concat({<[Patient Admitted?]={"1"}>} [Total Wait], ',') & ',' & Concat {<[ADMISSION FLAG]={"Y"}>} CONCLUSION_WAIT_MINS, ','))

Note that this is the 95%-ile, not the 50%-ile, and therefore is not the median. If you want the median, change the 0.95 to 0.5.

(Oh, and I would get the expression working correctly before wrapping it in a Time() statement. You can do that once its working)

Regards

Jonathan