How to median(Distinct x)

[#1]

Hi

I have to produce avg and median results for my data. The avg is only correct if I do avg(distinct x). but you can not distinct median.

I want median revenue of a salesman but the problem is a sales man can have multiple regions. So table shows the salesmans revenue as a separate field for each region. I am having the same problem with the stdev() function, does anyone know of a workaround?

Salesman	Region	Total Revenue
John	Newyork	233
John	Texas	233
John	Boltimore	233
Mike	Newyork	506
Mike	Nebraska	506
Ben	Texas	125

the Avg should be (233+506+125)/3

Median should be 233.

[swuehl]

I would suggest using a table that contains a revenue per Salesman, maybe like this:

INPUT:

LOAD Salesman,

     Region,

     [Total Revenue]

FROM

[http://community.qlik.com/thread/69764?tstart=0]

(html, codepage is 1252, embedded labels, table is @1);

REV:

LOAD DISTINCT

     Salesman,

     [Total Revenue] as TotalRevenue

Resident INPUT;

drop field [Total Revenue];

[#3]

hi

see the attached file.