Fill Values for a Field

[microwin88x]

Hello,

I have the following table:

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 field in my table as: AGGR(MEDIAN(SALES),GROUP). This is for every GROUP.

The thing is I'd need to fill that MEDIAN value for every GROUP and compare if SALES > MEDIAN then STATUS = 1, if not 0:

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

Finally count those CODES with STATUS = 1 and STATUS = 0:

CODE > MEDIAN (STATUS = 1)	8
CODE < MEDIAN (STATUS = 0)	9

Do you know how could I do that?

Thanks.

[ziadm]

Hi micro

Try this in script .. you can achieve the same on the front end Table

// This is on the script

Table1:

load * Inline [

GROUP, CODE, SALES

12, 1, 29284

12, 2, 9230

12, 3, 18461

12, 4, 63907

12, 5, 7292

12, 6, 6553

12, 7, 7376

12, 8, 11076

12, 9, 6769

12, 10, 8307

16, 26, 9461

16, 27, 10984

16, 28, 8461

16, 29, 9223

17, 30, 17076

17, 31, 12923

17, 32, 15076 ];

Left Join

load GROUP,

Median(SALES) as MEDAIN

Resident Table1

Group by GROUP;

NoConcatenate

DataTable:

LOAD *,

if (SALES > MEDAIN,1,0) as STATUS

Resident Table1;

[microwin88x]

Hello,

Isn't there any way to do it directly in my chart as an expression?

Because actually the median is dynamic and could be affected by multiple variables...

[ziadm]

Yes ..

Create a straight table and your dimension is GROUP

expression

Median(SALES)

if(Median(SALES) > SALES,1,0)

[ziadm]

you could the expression

Agg(Median(SALES),GROUP)

[devarasu07 · Accepted solution]

Hi Micro,

Seems same requirements, solutions already provided. help to close this duplicated thread. Thanks

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

Best Regards,

Deva