Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

Median on rolling 7 values...

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


2 Replies
MVP & Luminary
MVP & Luminary

Re: Median on rolling 7 values...

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


talk is cheap, supply exceeds demand
meet_purushu
New Contributor III

Re: Median on rolling 7 values...

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.

Community Browser