topic Re: Calculate Median in QlikView

Calculate Median

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a table with the following:

GROUP	CODE	SALES	MEDIAN
12	1	29284,6	-
12	2	9230,8	-
12	3	18461,5	-
12	4	63907,7	8.769
12	5	7292,3	-
12	6	6553,8	-
12	7	7376,9	-
12	8	11076,9	-
12	9	6769,2	-
12	10	8307,7	-
16	26	9461,5	-
16	27	10984,6	9.342
16	28	8461,5	-
16	29	9223,1	-
17	30	17076,9	15.077
17	31	12923,1	-
17	32	15076,9	-

I calculate the MEDIAN as an expression in my table as: AGGR(MEDIAN(SALES),GROUP). This is for every GROUP.

The thing is that I only get the value for 1 from the GROUP and I'd need to fill the nulls with the same value for the GROUP.

Like this:

29284,6

8.769

9230,8

8.769

18461,5

8.769

63907,7

8.769

7292,3

8.769

6553,8

8.769

7376,9

8.769

11076,9

8.769

6769,2

8.769

8307,7

8.769

9461,5

9.342

10984,6

9.342

8461,5

9.342

9223,1

9.342

17076,9

15.077

12923,1

15.077

15076,9

15.077

How can I do that directly on my simple table?

Thanks

Re: Calculate Median

devarasu07 — Thu, 27 Jul 2017 03:10:56 GMT

Hi,

Try like this,

AGGR(MEDIAN(total <GROUP>SALES),GROUP,CODE)

Re: Calculate Median

microwin88x — Thu, 27 Jul 2017 03:14:46 GMT

Thanks! I tried it and it works.

It'd be the same to use the following?:

MEDIAN(TOTAL<GROUP>SALES)

Cause it seems to be OK aswell. Could you check that out for me?

Re: Calculate Median

devarasu07 — Thu, 27 Jul 2017 03:18:23 GMT

Hi Micro,

Yeah, it should be ok. Tks

Re: Calculate Median

microwin88x — Thu, 27 Jul 2017 03:24:27 GMT

One last question Devarasu,

If I'd have to count how many CODES are greater than the associated Median for every row (comparing Sales with its Median),

Do you know how to do that?

I need to show it as a Text box with the number.

Thanks again!

Re: Calculate Median

devarasu07 — Thu, 27 Jul 2017 03:36:17 GMT

Hi,

Try like this,

=count({<CODE= {"=sum(SALES) > MEDIAN(TOTAL<GROUP>SALES)"} > } CODE)

Re: Calculate Median

microwin88x — Thu, 27 Jul 2017 04:01:19 GMT

It worked for most of cases, I found 2 of them that should have value = 1.

Do you know what it could be?

GROUP	CODE	(SALES)	MEDIAN	Status
			9461	8
12	1	29284	8768,5	1
12	2	9230	8768,5	0 > Shouldn't be 1? 9230 > 8768
12	3	18461	8768,5	1
12	4	63907	8768,5	1
12	5	7292	8768,5	0
12	6	6553	8768,5	0
12	7	7376	8768,5	0
12	8	11076	8768,5	1
12	9	6769	8768,5	0
12	10	8307	8768,5	0
16	26	9461	9342	0 > Shouldn't be 1? 9461> 9342
16	27	10984	9342	1
16	28	8461	9342	0
16	29	9223	9342	0
17	30	17076	15076	1
17	31	12923	15076	1 > Should be 0
17	32	15076	15076	1 > Should be 0 (Its 😃

Re: Calculate Median

devarasu07 — Thu, 27 Jul 2017 04:20:56 GMT

Can you try like below

=count({<CODE= {"=sum(SALES) >= median(total <GROUP> aggr(sum(SALES),CODE))"} > } CODE)

Re: Calculate Median

microwin88x — Thu, 27 Jul 2017 04:38:11 GMT

Almost... But thanks for the help!

I think it has something to do with the agroupation between the Group and Code...

Re: Calculate Median

devarasu07 — Thu, 27 Jul 2017 04:47:55 GMT

Hi,

Last try this, It should work

=count({<CODE= {"=sum(SALES) >= Aggr(Median(Total <GROUP>SALES),GROUP,CODE)"} > } CODE)

Hope this helps to you

Best Regards,

Deva

Re: Calculate Median

microwin88x — Thu, 27 Jul 2017 05:07:45 GMT

Yes!!! Thank you very much! Worked like a charm!

To get the Median value fixed without getting affected by selections from other fields, should I add the following?:

AGGR(MEDIAN({<FIELD1=,FIELD2=,FIELD3=>} total <GROUP>SALES),GROUP,CODE)

Or do you know any other way instead of specifying all the fields to omit?

Thanks again!

Re: Calculate Median

devarasu07 — Thu, 27 Jul 2017 05:37:01 GMT

Hi,

if you wish to ignore all field selection u can simply use Set identifier 1 or only specific field then use FIELD1= like that

=count({1<CODE= {"=sum(SALES) >= Aggr(Median(Total <GROUP>SALES),GROUP,CODE)"} > } CODE)

Thanks,Deva