topic Re: Median Calculation in QlikView

Median Calculation

PradeepReddy — Wed, 26 Mar 2014 17:23:04 GMT

Hi,

I had a simple requirement, but not getting the solution. May be missing something..

Scenario:

There is 3 fields in one table.(Fields: Firm_Name, Time_Period, Hours). I want to calculate the Median of the Hours and correponding Firm_Name.

Dimension : Time_Period

Expressions: Median, Firm_Name( corresponding to the Median value)

Functionality:

If the count is Odd number then there is no issue.--- Median(Hours)

If the count is Even then, the median value doesn't match to any existing Hours value. So I am not able to display the Firm_Name. So I want to dispaly the middle 2 values and its correposnding Firm_Name.

Ex: 6,9,2,5,10,7 (After sorting... 2,5,6,7,9,10)

Here I want to display the values 6 & 7 and its coresponding Firm_Name.

Thanks in advance..

Re: Median Calculation

rbecher — Wed, 26 Mar 2014 17:50:44 GMT

Hi,

I think Firm_Name should be a dimension? Or do I get something wrong?

- Ralf

Re: Median Calculation

PradeepReddy — Wed, 26 Mar 2014 17:56:39 GMT

Hi Ralf,

Dimension is Time_Period.

Re: Median Calculation

rbecher — Wed, 26 Mar 2014 18:00:58 GMT

Can you upload sample data and the expected result?

Re: Median Calculation

PradeepReddy — Wed, 26 Mar 2014 18:26:01 GMT

Here is the sample data...

Firm_Name	Hours	Time_Period
Firm1	12	Period1
Firm2	10	Period1
Firm4	14	Period1
Firm9	45	Period2
Firm10	65	Period2
Firm11	33	Period2
Firm15	78	Period2
Firm12	87	Period2
Firm18	45	Period2
Firm19	70	Period3
Firm22	55	Period3
Firm23	76	Period3

Expected output would be like below...

Time_Period	Median	Firm Name
Period1	12	Firm1
Period2	65 78	Firm10 Firm15
Period3	70	Firm19

I am getting the result, If the count is Odd number. (using Median(), FieldIndex(), FieldValue()..)

But not getting for the Even count....

Thanks in advance..

Re: Re: Median Calculation

rbecher — Wed, 26 Mar 2014 19:43:43 GMT

Please find attached solution. But the Median of Period2 = 55, so no match with a Firm!

- Ralf

Re: Re: Median Calculation

PradeepReddy — Thu, 27 Mar 2014 03:34:59 GMT

Ralf,

thanks for your quick support....

yes you are right... that is the tricky one

IF Count(Hours) is odd , then it is straight forward.. no issue in this case...

(in this case Median of Period2 should exists exists in the list of hours)

IF Count(Hours) is even, then we need to display the Firm_Name of middle 2 values... as shown above.

(in this case Median of Period2 may or may not exists in the list of hours)

Regards,

Pradeep

Re: Median Calculation

PradeepReddy — Fri, 28 Mar 2014 10:33:57 GMT

Jagan Mohan

can you please help on this?

Re: Re: Median Calculation

rbecher — Fri, 28 Mar 2014 14:34:03 GMT

Hi,

this is what I have so far. But it's not working for the upper middle (Period2=65). I think this is a set analysis isssue..

- Ralf

Re: Re: Median Calculation

PradeepReddy — Sat, 29 Mar 2014 17:31:19 GMT

If we can achieve this using set analysis, can you please assist me how to achieve this..?