Qlik Community

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 :-

DateBUG_IDAge
29-Jan-15266634
4-Feb-15231328
4-Feb-152097531
24-Feb-152015354
2-Mar-15230916
2-Mar-15155828
2-Mar-152103523

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 :-

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

Regards,

KKR

15 Replies
MVP & Luminary
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

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
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

Re: Running Median calculation

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

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:

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;


Capture.PNG

sasiparupudi1
Honored Contributor III

Re: Running Median calculation

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

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:

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;

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