Running Median calculation

[#1]

Hi All,

Need help in calculating Running median in a Line chart. Requirement as below. For a given date, the expression should consider all the Age values till that date and calculate the median. For all the missing dates from min date to max date, previous date Median value should be repeated. Please let me know if you need more information.

Data :-

Date	BUG_ID	Age
29-Jan-15	26663	4
4-Feb-15	23132	8
4-Feb-15	20975	31
24-Feb-15	20153	54
2-Mar-15	23091	6
2-Mar-15	15582	8
2-Mar-15	21035	23

Calculation :-

On 29-Jan-15 -> Median is 4

On 4-Feb-15 -> Median (4,8,31) = 8

On 24-Feb-15 -> Median(4,8,31,54) = 19.5

On 2-Mar-15 -> Median(4,8,31,54,6,8,23) = 8

Result :-

Date	Median Age
29-Jan-15	4
30-Jan-15	4
31-Jan-15	4
1-Feb-15	4
2-Feb-15	4
3-Feb-15	4
4-Feb-15	8
5-Feb-15	8
6-Feb-15	8
7-Feb-15	8
8-Feb-15	8
9-Feb-15	8
10-Feb-15	8
11-Feb-15	8
12-Feb-15	8
13-Feb-15	8
14-Feb-15	8
15-Feb-15	8
16-Feb-15	8
17-Feb-15	8
18-Feb-15	8
19-Feb-15	8
20-Feb-15	8
21-Feb-15	8
22-Feb-15	8
23-Feb-15	8
24-Feb-15	19.5
25-Feb-15	19.5
26-Feb-15	19.5
27-Feb-15	19.5
28-Feb-15	19.5
1-Mar-15	19.5
2-Mar-15	8

Regards,

KKR

[marcus_sommer]

You could calculate this with a combination from range- und inter-record-functions like this:

rangeavg(above(Age, 0, rowno(total)))

or maybe better with a inside-aggregation

rangeavg(above(avg(Age), 0, rowno(total)))

See also postings like: What is the means by rangeavg(above(Sum([Billing Sales Amount_NETWR]),0,3))

- Marcus

[sunny_talwar]

This would get RangeAvg, is there a function like RangeMedian?? I have been trying this for few hours now, but not able to get the exact result.

[marcus_sommer]

I'm not sure if this is excactly what you are looking for but by these example:

rangefractile (0.5, above(sum(x),-1,3))

explained the help it would be the median for the last three sum-results.

- Marcus

[#5]

As sunindia‌ mentioned, I need Median, not Average.

Regards,

KKR

[sunny_talwar]

Hahahaha you are right , RangeFractile should do the job here.

[sunny_talwar]

Since I was working on this for hours, I am going to share it, even though there is a easier way to do it

Table:

LOAD Date(Date#(Date, 'D-MMM-YY')) as Date,

  BUG_ID,

  Age

Inline [

Date, BUG_ID, Age

29-Jan-15, 26663, 4

4-Feb-15, 23132, 8

4-Feb-15, 20975, 31

24-Feb-15, 20153, 54

2-Mar-15, 23091, 6

2-Mar-15, 15582, 8

2-Mar-15, 21035, 23

];

Temp: 

Load min(Date) as minDate, 

    max(Date) as maxDate 

Resident Table; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

Join (Table) 

LOAD Date($(varMinDate) + IterNo() - 1) as Date 

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Join(Table)

LOAD Date,

  Concat(Age, ',') as ConcatAge

Resident Table

Group By Date;

Temp:

LOAD Distinct Date,

  ConcatAge

Resident Table;

DROP Table Table;

Temp1:

NoConcatenate

LOAD Date,

  If(RowNo() = 1, ConcatAge, If(Len(Trim(ConcatAge)) = 0, Peek(ConcatAge), ConcatAge & ',' & Peek(ConcatAge))) as ConcatAge

Resident Temp

Order By Date;

Temp2:

NoConcatenate

LOAD Date,

  SubField(ConcatAge, ',') as ConcatAge

Resident Temp1;

NewTable:

LOAD *,

  If(Count = 1, SubField(ConcatAge, ',', 1), If(Odd(Count), SubField(ConcatAge, ',', Ceil(Count/2)),

  (Num#(SubField(ConcatAge, ',', (Count/2)), '##') + Num#(SubField(ConcatAge, ',', ((Count/2) + 1)), '##'))/2)) as Median;

LOAD *,

  Len(KeepChar(ConcatAge, ',')) + 1 as Count;

NoConcatenate

LOAD Date,

  Concat(ConcatAge, ',', ConcatAge) as ConcatAge

Resident Temp2

Group By Date;

DROP Tables Temp, Temp1, Temp2;

[sasiparupudi1]

t97:

LOAD date#(xDate,'DD-MMM-YY') as xDate,BUG_ID,Age Inline

[

xDate,BUG_ID,Age

29-Jan-15,26663,4

4-Feb-15,23132,8

4-Feb-15,20975,31

24-Feb-15,20153,54

2-Mar-15,23091,6

2-Mar-15,15582,8

2-Mar-15,21035,23

];

NoConcatenate

t96:

LOAD xDate,Age,if (isnull(peek('x'))=0,Age &','&peek('x'),Age)  as x Resident t97 order by xDate asc;

NoConcatenate

t95:

LOAD    xDate,  minstring(x)  as x   Resident t96  Group by xDate Order by xDate Asc,x desc;

NoConcatenate

t94:

LOAD    xDate,  SubField(x,',')  as x   Resident t95  ;

NoConcatenate

t93:

LOAD    xDate,  median(x) as x   Resident t94 Group by xDate ;

drop Table t97,t96,t95,t94;

hth

Sasi

[Kushal_Chawda]

try this,

Median function in the script is not working as required. In front end when you do the selection on date median(Age) is working fine, but this will not be a good solution, so I followed below method

Data:

LOAD *,date(Date#(Date,'DD-MMM-YYYY'),'DD-MMM-YYYY') as DATE Inline [

Date, BUG_ID, Age

29-Jan-15, 26663, 4

4-Feb-15, 23132, 8

4-Feb-15, 20975, 31

24-Feb-15, 20153, 54

2-Mar-15, 23091, 6

2-Mar-15, 15582, 8

2-Mar-15, 21035, 23 ];

for i=1 to FieldValueCount('DATE')

let vDate = FieldValue('DATE',$(i));

Median:

LOAD Distinct

Median(Age) as Median1,

num(Date#('$(vDate)','DD-MMM-YYYY')) as DATE_NEW

Resident Data

where DATE<= Date#('$(vDate)','DD-MMM-YYYY');

NEXT

MaxMin:

LOAD max(DATE_NEW) as MaxDate,

min(DATE_NEW) as MinDate

Resident Median;

let vMaxDate = Peek('MaxDate',0,'MaxMin');

//let vMinDate = Peek('MinDate',0,'MaxMin');

Join (Median)

LOAD distinct DATE_NEW + IterNo()-1 as DATE_NEW

Resident Median

While DATE_NEW + IterNo()-1 <= '$(vMaxDate)';

Final:

NoConcatenate

LOAD date(DATE_NEW,'DD-MMM-YYYY') as DATE,

if( rowno()=1,Median1,if(IsNull(Median1),Peek('Median'),Median1)) as Median

Resident Median

Order by DATE_NEW asc;

DROP Table Median;

[#10]

sunindia‌, Kush141087‌ ,

I have simplified the table for the purpose of explanation. Actual table has other fields like Project, Application etc using which user will slice the data. I am afraid that I cannot precalculate the median at script level. This needs to be done in the presentation layer.

The expression Marcus_Sommer‌ suggested didn't work for me.

sunindia‌, Please let me know the easier way you were mentioning.

Regards,

KKR