2 Replies Latest reply: Mar 13, 2015 4:54 AM by Purushotham M RSS

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

       


        • Re: Median on rolling 7 values...
          Gysbert Wassenaar

          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...
            Purushotham M

            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.