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

[Kushal_Chawda]

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?

[#3]

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

Regards,

KKR

[sunny_talwar]

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

[sunny_talwar]

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:

[sunny_talwar]

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:

[#7]

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