Weighted median

[mjamesking]

Can anyone suggest the best way to calculated a weighted median in QlikView

The data i have is in the following format.

Value, Weight

2, 3

7, 5

9, 5

13, 2

The weighted median is 7 as illustrated below.

2 2 2 7 7 7 7 7 9 9 9 9 9 13 13

Can anyone think of the best way to calculated this in QlikView?

Thanks

Matt

[dirk_fischer]

Hi Marco,

I'm aware that this discussion took place some time ago, but I still hope to get some help from you or another expert.

I tried to implement your formula, but I don't get the correct results, if I use the Aggr function.

The table below shows the results with and without aggr as well as accumulated percentage of measurement points.

I use the following implementation of your formula:

Median with outer Aggr

Aggr({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}, ERROR={'MY ERROR}>}

     If(

        ((RangeSum(Above(Sum(HAEUFIGKEIT),0,RowNo())) /

          Sum(TOTAL HAEUFIGKEIT))>=0.5) And

        ((RangeSum(Above(Sum(HAEUFIGKEIT),1,RowNo()-1)) /

          Sum(TOTAL HAEUFIGKEIT))<0.5),

        ERGEBNIS

        ),

      ERGEBNIS)

Median without Aggr

If((RangeSum(Above(Sum({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}, ERROR={'MY ERROR'}>} HAEUFIGKEIT),0,RowNo())) /

         Sum(TOTAL {<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}, ERROR={'MY ERROR'}>} HAEUFIGKEIT)>=0.5) And

        (RangeSum(Above(Sum({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}, ERROR={'MY ERROR'}>} HAEUFIGKEIT),1,RowNo()-1)) /

         Sum(TOTAL {<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}, ERROR={'MY ERROR'}>} HAEUFIGKEIT)<0.5),

        ERGEBNIS

   )

I know, that it's not the set Analysis, because even without Set Analysis and manually selecting the same values, I get 57 where I would expect 56 as median.

Any suggestions where this comes from? It looks a bit like the aggr function and the RowNo would get confused or something like that.

Any ideas would be great. Thank you very much for your help.

Best regards

Dirk

[dirk_fischer]

Hi Marco,

I just found it myself. I had to add a structured parameter and define the sorting for the Aggr function. Now I get the expected result.

Attached the formula, which is working correctly:

Aggr({<COLLECTION={'SENSOR MEASUREMENTS'},RESULT_TYPE={'number'}, ERROR={MY ERROR'}>}

     If(

        ((RangeSum(Above(Sum(HAEUFIGKEIT),0,RowNo())) /

          Sum(TOTAL HAEUFIGKEIT))>=0.5) And

        ((RangeSum(Above(Sum(HAEUFIGKEIT),1,RowNo()-1)) /

          Sum(TOTAL HAEUFIGKEIT))<0.5),

        ERGEBNIS

        ),

     (ERGEBNIS,(NUMERIC,ASCENDING))

    )

Best regards,

Dirk

[MarcoWedel]

nice to hear that it works for you.

Thanks for sharing.

Marco

[Greg_Oliven]

The RangeFractile solution is really smart, and works correctly to calculate a weighted Median. However, when the list of values that is created via Repeat and Concat gets quite large (maybe in he hundreds of thousands of values range), the Qlik Sense app gets bogged down waiting for the whole calculation to finish. 

Has anyone founds a way to calculate a weightedMedian that scales better?