topic Re: Median on rolling 7 values... in Qlik Learning Discussions

Median on rolling 7 values...

Wed, 04 Mar 2015 02:06:33 GMT

Hi, I am trying to come up with the Median value (PIR_POR_DURATION) on the last 7 records in a table. The last 7 records would be qualified by date (most recent 7) and must have same Procedure and same Surgeon which are dimensions in my table/ And if possible I woul like to get this median value only after 10 records have been qualified, meaning there must be historical 10 records before taking the median value on the 7 most recent. I also am unsure whether I need the accumulation radio buttons in addition to the correct formula. I was trying something with RangeFractile based on my review of previous discusssions. I am fairly beginner on Qlikview. Any help would be much appreciated!! I have attached the output I am hoping to achieve. Notice only 2 procedures meet the 10 minimum requirement. If this part is too difficult, I would be happy with the median value on previous 7 cases (same surgeon, same procedure). Thank you!

RangeFractile(.5,Above( [PIR_POR_DURATION], 0,7))

Re: Median on rolling 7 values...

Gysbert_Wassenaar — Wed, 04 Mar 2015 09:54:17 GMT

Maybe like this:

Data:
LOAD *,
  if(Counter>10, RangeFractile(0.5, peek([PIR-POR],-1),
                                    peek([PIR-POR],-2),
                                    peek([PIR-POR],-3),
                                    peek([PIR-POR],-4),
                                    peek([PIR-POR],-5),
                                    peek([PIR-POR],-6),
                                    peek([PIR-POR],-7))) as [Median PIR-POR];
LOAD Facility,
    SERVICE,
    PROCEDURE_DATE_DT,
    Surgeon,
    PROCEDURE_NAME,
    [Sched  Dur],
    [PIR-PST],
    [PST-PF],
    [PF-POR],
    [PIR-POR],
    AutoNumber(RecNo(), Surgeon & '|' & PROCEDURE_NAME) as Counter
FROM [myexcelfile.xls](biff, embedded labels, table is Sheet1$);

Re: Median on rolling 7 values...

Anonymous — Fri, 13 Mar 2015 08:54:47 GMT

Hi,
First We have to Group and Sort the record in Load Script for specific dimensions in your case below order.
1.Date
2.Procedure
3.Surgeon
Then use the Range function for better results.