Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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.
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
Hahahaha you are right , RangeFractile should do the job here.
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;
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
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;
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