topic Re: Weighted median in QlikView

Weighted median

mjamesking — Tue, 29 Sep 2015 20:37:59 GMT

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

Re: Weighted median

elyM — Tue, 29 Sep 2015 21:10:52 GMT

Hi Matt,

you need to use Median function.

median ([{set_expression}] [ distinct ] [ total [<fld {,fld}>] ] expression )

Median([Value])

Regards,

Ely

Re: Weighted median

mjamesking — Tue, 29 Sep 2015 21:20:03 GMT

Thanks Ely,

But that won't take the weight into account, the weighted median is 7, just doing median of Value gives me 8

Any ideas?

Cheers

Matt

Re: Weighted median

elyM — Tue, 29 Sep 2015 21:32:04 GMT

Hi Matt,

I have created a new QlikView document with your list of value, 15 not distinct values in field.

Check return number of this function Count([Value])

Regards,

Ely

Re: Weighted median

mjamesking — Tue, 29 Sep 2015 21:40:18 GMT

Thank Ely - I have the same here with the expanded list, I was wondering if there if there was a way of using the two fields in my data

Value, Weight

2, 3

7, 5

9, 5

13, 2

to come up with the same answer in one expression. Or whether (as you and I have done) I need to first expand the data?

If the only way to do this is to expand the data into the list of 15 values can you think of a quick way of doing this in a script without looping through the list with peek, which would be very slow with high data volumes. Also the weight my not necessarily be a whole number in every scenario that we may want to use this?

Matt

Re: Weighted median

sunny_talwar — Tue, 29 Sep 2015 22:03:53 GMT

Try this script:

Table:

LOAD * Inline [

Value, Weight

2, 3

7, 5

13, 2

9, 5

];

NewTable:

LOAD *,

RangeSum(Weight, Peek('CumWeight')) as CumWeight

Resident Table

Order By Value;

LET vTotalWeight = Peek('CumWeight');

LET vMedian1 = If(Odd($(vTotalWeight)), Ceil($(vTotalWeight)/2), $(vTotalWeight)/2);

LET vMedian2 = If(Odd($(vTotalWeight)), Ceil($(vTotalWeight)/2), ($(vTotalWeight)/2 + 1));

Temp1:

NoConcatenate

LOAD Value as Value1,

Weight

Resident NewTable

Where CumWeight <= $(vMedian1);

LET vMedianValue1 = Peek('Value1');

Temp2:

NoConcatenate

LOAD Value as Value2,

Weight

Resident NewTable

Where CumWeight <= $(vMedian2);

LET vMedianValue2 = Peek('Value2');

DROP Tables Temp1, Temp2, Table;

LET vMedian = ($(vMedianValue1) + $(vMedianValue2))/2;

The median value is saved in the variable vMedian.

Variable Overview:

When the data is changed so that the sum of weight is 14 and the median will be the average of 7th observation and 8th observation you get the following result ->

Re: Weighted median

MarcoWedel — Tue, 29 Sep 2015 22:10:15 GMT

Hi,

one solution could be:

RangeFractile(0.5,$(=Left(Concat(Repeat(Value&',',Weight)),Len(Concat(Repeat(Value&',',Weight)))-1)))

hope this helps

regards

Marco

Re: Weighted median

sunny_talwar — Tue, 29 Sep 2015 22:15:26 GMT

Hey Marco this is an amazing way to handle this. I have never used the repeat function before and was thinking of something like this in script. Is there a repeat equivalent of something in script?

Also, I am unable to open your attached qvw because I am using the PE, but can you check what is the median if this the data:

Table:

LOAD * Inline [

Value, Weight

2, 3

7, 5

13, 1

9, 5

];

I would expect it to be 4.5

Thanks,

Sunny

Re: Weighted median

MarcoWedel — Tue, 29 Sep 2015 22:33:06 GMT

Hi Sunny,

you can use repeat() in the script as well.

I tested your example data. Here's the result:

Qlik on

regards

Marco

Re: Weighted median

mjamesking — Tue, 29 Sep 2015 22:33:06 GMT

Thanks everyone.

Marco,

That's a great way to do it. Could you see this scaling with a lot of rows, or becoming slow? Also if the weights given aren't whole numbers, but numbers to a few decimal places can you think of a way to perform an equivalent calculation?

Matt

Re: Weighted median

MarcoWedel — Tue, 29 Sep 2015 22:40:33 GMT

Hi again,

Regarding the script implementation I had something in mind like:

LOAD Median(Value) as YourMedian;
LOAD Value
INLINE [
    Value, Weight
    2, 3
    7, 5
    13, 1
    9, 5
]
While IterNo()<=Weight;

Marco

Re: Weighted median

sunny_talwar — Tue, 29 Sep 2015 22:47:00 GMT

I think I don't like my solution anymore.

Thanks for sharing the script solution. I will have to spend some more time understanding how IterNo() works. I have thought about using it, but never really put time to see what it is doing.

Thanks Marco.

Re: Weighted median

elyM — Wed, 30 Sep 2015 15:12:43 GMT

Hi Matt,

try this quick backend solution:

Test:
NoConcatenate
Load
Value
,Weight
While
(Weight - IterNo())>=0;
Load
*
INLINE [
Value,Weight
2,3
7,5
9,5
13,2];

Re: Weighted median

mjamesking — Wed, 30 Sep 2015 18:20:51 GMT

This is, great. I'm getting there

Now, let me change the question slightly. If instead of an integer for weight, I'm given a decimal or a percentage.

So, data look like this:

Value, Weight

2, 0.2

7, 0.333333

9, 0.333333

13, 0.133333

How to tackle this? Both the solutions above the the expression and the iterative load are great, but rely on whole numbers.

Thanks

Matt

Re: Weighted median

elyM — Thu, 01 Oct 2015 07:52:48 GMT

Hi Matt,

first idea is to bring all Weight occurrences and calculate GCD (greatest common divisor) .

0.2 = 1/5 ->3

0.3333= 1/3 -> 5

0.1333 = 2/15 -> 2

beacuse GCD is 15

[Value,Weight

2,3

7,5

9,5

13,2]

Regards,

Ely

Re: Weighted median

mjamesking — Fri, 02 Oct 2015 16:54:46 GMT

Thanks Ely,

I now have the data - and as I expected I receive the weights as a percentage, not a whole number.

What I now want to do is to

Order the list of values by value, and calculate the cumulative weight. Highlight the first value where the cumulative weight reaches over 50% of the total weight for the data set (this is the weighted median). Very much like how Sunny did this in the script, but I would like to do this as part of a straight table - or more ideally have a formula that returns the value straight off.

I have this straight table

Ordering the values by value, using the following formula to calculate the running total

Running Weight: =Rangesum(Above(TOTAL Sum(Weight),0,rowno(TOTAL)))

The 50% threshold, Median Threshold: =SUM(TOTAL Weight)/2

And a simple IF statement to highlight where the Running weight is greater than the threshold.

I would like to highlight the the first row where the threshold is broken (in this case Value = 7).

I'm thinking I need an AGGR expression and to use FirstSortedValue, but cannot get anything to work.

I appreciate everyone's help, thanks.

Matt

Re: Weighted median

MarcoWedel — Sat, 03 Oct 2015 22:37:11 GMT

Hi,

maybe using a background colour expression like:

=If(Rangesum(Above(Weight,0,RowNo()))/Sum(TOTAL Weight)>=0.5 and Rangesum(Above(Weight,1,RowNo()-1))/Sum(TOTAL Weight)<0.5,Yellow())

hope this helps

regards

Marco

Re: Weighted median

mjamesking — Sat, 03 Oct 2015 23:24:36 GMT

Thanks Marco

That gives me the chart I was after. Great.

Any thoughts on how to just get the answer in an expression, without the need for a straight table? Some kind of aggr function?

Matt

Re: Weighted median

MarcoWedel — Sat, 03 Oct 2015 23:31:57 GMT

Hi,

maybe like:

Aggr(If(Rangesum(Above(Weight,0,RowNo()))/Sum(TOTAL Weight)>=0.5 and Rangesum(Above(Weight,1,RowNo()-1))/Sum(TOTAL Weight)<0.5,Value),Value)

hope this helps as well

regards

Marco

Re: Weighted median

mjamesking — Wed, 07 Oct 2015 05:04:40 GMT

Thanks for everyone's help, all answers very useful. One thing to be aware of especially with the final aggregation is order that the data is loaded into qlikview, the original order is what's important here, not any subsequent sorting in the script. I had to fool qlikview by renaming fields after sorting to get consistent sensible answers.