# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

## Running Median calculation

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

15 Replies
MVP & Luminary

## Re: Running Median calculation

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

MVP

## Re: Running Median calculation

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.

MVP & Luminary

## Re: Running Median calculation

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

Not applicable

## Re: Running Median calculation

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

Regards,

KKR

MVP

## Re: Running Median calculation

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

MVP

## Re: Running Median calculation

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:

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:

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)

Concat(Age, ',') as ConcatAge

Resident Table

Group By Date;

Temp:

ConcatAge

Resident Table;

DROP Table Table;

Temp1:

NoConcatenate

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

Resident Temp

Order By Date;

Temp2:

NoConcatenate

SubField(ConcatAge, ',') as ConcatAge

Resident Temp1;

NewTable:

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;

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

NoConcatenate

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

Resident Temp2

Group By Date;

DROP Tables Temp, Temp1, Temp2;

Honored Contributor III

## Re: Running Median calculation

t97:

[

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

MVP

## Re: Running Median calculation

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:

Median(Age) as Median1,

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

Resident Data

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

NEXT

MaxMin:

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

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

Resident Median

Order by DATE_NEW asc;

DROP Table Median;

Not applicable

## Re: Running Median calculation

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