topic Re: Running Median calculation in QlikView

Running Median calculation

Mon, 14 Sep 2015 08:20:58 GMT

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

Re: Running Median calculation

marcus_sommer — Mon, 14 Sep 2015 11:03:40 GMT

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

- Marcus

Re: Running Median calculation

sunny_talwar — Mon, 14 Sep 2015 11:16:10 GMT

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.

Re: Running Median calculation

marcus_sommer — Mon, 14 Sep 2015 11:24:34 GMT

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

Re: Running Median calculation

Mon, 14 Sep 2015 11:24:54 GMT

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

Regards,

KKR

Re: Running Median calculation

sunny_talwar — Mon, 14 Sep 2015 11:29:17 GMT

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

Re: Running Median calculation

sunny_talwar — Mon, 14 Sep 2015 11:34:12 GMT

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;

Re: Running Median calculation

sasiparupudi1 — Mon, 14 Sep 2015 12:30:14 GMT

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

Kushal_Chawda — Mon, 14 Sep 2015 14:14:42 GMT

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');

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;

Re: Running Median calculation

Tue, 15 Sep 2015 05:13:31 GMT

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

Re: Running Median calculation

Kushal_Chawda — Tue, 15 Sep 2015 05:26:21 GMT

kumar_ilb

It should be possible to calculate the Median at script level. On presentation layer it will be more complex.

Can you share the sample data with other fields also?

Re: Running Median calculation

Tue, 15 Sep 2015 07:00:15 GMT

Attached sample data and expected result set. Let me know if you need more information.

Regards,

KKR

Re: Running Median calculation

sunny_talwar — Tue, 15 Sep 2015 07:01:41 GMT

Here is a front end solution. See if this is useful:

Expression: =If(Count({<Date = >}TOTAL <Date> Date) = RowNo(), RangeFractile(0.50, Above(TOTAL Only({<Date = >} Age), 0, RowNo(TOTAL))))

Dimensions: Date and BUG_ID

Note: Used BUG_ID to create a unique list of Age for each row and then hid the dimension from the presentation tab. If in your application you will have another dimension which makes Age unique for each row, you will probably need to add that to your chart.

Attaching the qvw for you to review.

HTH

Best,

Sunny

Re: Running Median calculation

sunny_talwar — Tue, 15 Sep 2015 07:54:06 GMT

Without selection it is working the way you want, still need to figure it out after selection.

Script:

Table:

LOAD PROJECT_ID,

APPLICATION,

VERSION,

BUD_ID,

FIND_DATE,

CLOSE_DATE,

CLOSE_DATE - FIND_DATE as Age

FROM

[FD SampleData.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Temp:

Load min(FIND_DATE) as minDate,

max(CLOSE_DATE) as maxDate

Resident Table;

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

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

DROP Table Temp;

MasterCalendar:

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

AutoGenerate 1

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

Straight Table

Dimension: CLOSED_DATE

BUD_ID

Expression:

=If(Count({<CLOSE_DATE = >}TOTAL <CLOSE_DATE> CLOSE_DATE) = RowNo(),

If(Len(RangeFractile(0.50, Above(TOTAL Only({<CLOSE_DATE = >} Age), 0, RowNo(TOTAL)))) = 0, 0.0000000001,

RangeFractile(0.50, Above(TOTAL Only({<CLOSE_DATE = >} Age), 0, RowNo(TOTAL)))))

Output:

Re: Running Median calculation

sunny_talwar — Tue, 15 Sep 2015 08:03:15 GMT

Here you go. Seems to be working the way you want, but I leave it for you to do the final testing

Same Script as Above. Dimension remains the same. Only the expression changes:

=If(Count({<CLOSE_DATE = , PROJECT_ID = >}TOTAL <CLOSE_DATE> CLOSE_DATE) = RowNo(),

If(Len(RangeFractile(0.50, Above(TOTAL Only({<CLOSE_DATE = >} Age), 0, RowNo(TOTAL)))) = 0, 0.0000000001,

RangeFractile(0.50, Above(TOTAL Only({<CLOSE_DATE = >} Age), 0, RowNo(TOTAL)))))

Screenshot after Selection:

Re: Running Median calculation

Thu, 24 Sep 2015 18:58:17 GMT

I have created a new Date table with two columns Date_FD and DateKey. Date_FD contains dates from minimum CLOSE_DATE to maximum. For each Date_FD, DateKey will contain dates from minimum CLOSE_DATE to the Date_FD value. Below is the script.

In line chart, used Date_FD as dimension and Median(Age) as expression.

DATA:
LOAD PROJECT_ID,
     APPLICATION,
     VERSION,
     BUD_ID,
     FIND_DATE,
     CLOSE_DATE,
     CLOSE_DATE as DateKey,
     CLOSE_DATE - FIND_DATE as Age
FROM
[FD SampleData.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

MAX_MIN_TEMP:
LOAD
    Min(CLOSE_DATE) as MIN_DATE,
    Max(CLOSE_DATE) as MAX_DATE
Resident DATA;

LET vMinDate = Num(Peek('MIN_DATE'));
LET vMaxDate = Num(Peek('MAX_DATE'));

FOR i=$(vMinDate) to $(vMaxDate)

FD_CALENDAR:
LOAD
    Date($(i)) as Date_FD,
    Date($(vMinDate)+RecNo()-1) as DateKey
AutoGenerate $(i)-$(vMinDate)+1;   

NEXT   

DROP Table MAX_MIN_TEMP;

Regards,

KKR