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

    How to calculate the Median of 2 values concatenated together

    Helen Pippard

      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
          Rahul Lakhina

          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
            Jonathan Dienst

            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
                Helen Pippard

                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

                 

                 

                 

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

                    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