Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
| |
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 | 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
Hi,
Try like this,
AGGR(MEDIAN(total <GROUP>SALES),GROUP,CODE)
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?
Hi Micro,
Yeah, it should be ok. Tks
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!
Hi,
Try like this,
=count({<CODE= {"=sum(SALES) > MEDIAN(TOTAL<GROUP>SALES)"} > } CODE)
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 😃 |
Can you try like below
=count({<CODE= {"=sum(SALES) >= median(total <GROUP> aggr(sum(SALES),CODE))"} > } CODE)
Almost... But thanks for the help!
I think it has something to do with the agroupation between the Group and Code...
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