Calculate Median

[microwin88x]

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:

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

How can I do that directly on my simple table?

Thanks

[devarasu07 · Accepted solution]

Hi,

Try like this,

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

[microwin88x]

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?

[devarasu07]

Hi Micro,

Yeah, it should be ok. Tks

[microwin88x]

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!

[devarasu07]

Hi,

Try like this,

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

[microwin88x]

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 😃

[devarasu07]

Can you try like below

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

[microwin88x]

Almost... But thanks for the help!

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

[devarasu07]

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