Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
helen_pip
Contributor II

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

1 Solution

Accepted Solutions
MVP
MVP

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
Not applicable

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

MVP
MVP

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
helen_pip
Contributor II

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

(Fractile({<[Patient Admitted?]={"1"}>}[Total Wait],0.95)/1440
+
Time(fractile({<[ADMISSION FALG]={"Y"}>}, [CONCLUSION_WAIT_MINS]),0.95)/1440), 'hh:mm')

 

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

MVP
MVP

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser