topic Find the 95th record by using expression in QlikView

Find the 95th record by using expression

Wed, 15 Jun 2016 11:17:06 GMT

Hi I have a scenario where I have to find the Leatime and the no of orders went through.

From the below image I have

Hour field=> which is calculated by interval of two dates.

Order=> is the count of orders for every hour

Rolling=>is the rangesum

Percentage=> eg 85709/Total(135865)

Help: I need to locate the 94% and the Order and hour where 94% of orders contribute

Same the screenshot

Re: Find the 95th record by using expression

sunny_talwar — Wed, 15 Jun 2016 13:51:02 GMT

You may be able to use FirstSortedValue() with set analysis. the exact expression would be difficult to speculate without looking at a sample. Would you be able to provide one?

Re: Find the 95th record by using expression

sunny_talwar — Wed, 15 Jun 2016 13:55:50 GMT

Try these:

Order

=FirstSortedValue({<Hour = {"=RangeSum(Above(Sum(Order), 0, RowNo()))/Sum(TOTAL Order) < 0.95"}>} Order, -Hour)

Hour:

=Max({<Hour = {"=RangeSum(Above(Sum(Order), 0, RowNo()))/Sum(TOTAL Order) < 0.95"}>} Hour)

Re: Find the 95th record by using expression

Digvijay_Singh — Wed, 15 Jun 2016 13:57:58 GMT

Not sure but you can try using background color expression(drill '+' sign in percent exp) same as percent exp and compare it to 94% to highlight the percentage column row where it meets the criteria..

Re: Find the 95th record by using expression

Wed, 15 Jun 2016 17:35:00 GMT

Thank you very much for you help Sunny

Re: Find the 95th record by using expression

Wed, 22 Jun 2016 17:30:50 GMT

Hi Sunny, There is one more problem I am facing I need to plot the 95th hour on a chart based on a date is it possible to show the 95th hour for every day Attached the data in excel file with reference to your formula

Re: Find the 95th record by using expression

sunny_talwar — Thu, 23 Jun 2016 02:47:38 GMT

Arjun how the Excel file attached related to the screenshot you posted. I am having difficulty finding the relation between the two. I think it would be helpful if you can elaborate your request

Re: Find the 95th record by using expression

Thu, 23 Jun 2016 03:48:53 GMT

Hi Sunny

sorry for not elaborating, I need to find the max hours for every day for eg

May month I need to find the max hours for the 95th order and plot that on the chart as shown in the below image

Excel file attached is the result from the qlikview, and I am trying to get the max hours for every day

I have also attached the excel sheet of the result what I want marked yellow

I am trying to find a way to do this, your solution was so helpful,but with this scenario for the past three to four days I am trying a way to achieve this but I am unsuccessful.

Your help will be really a lifesafer

looking forward for your reply

Regards

Re: Find the 95th record by using expression

sunny_talwar — Fri, 24 Jun 2016 19:43:59 GMT

Arjun -

1) There is no date field in your attached Excel file.

2) Also you mentioned that you have marked yellow for the result, but I don't see any yellow color. Are you sure you attached the right Excel file?

Sorry, I wasn't able to offer much help before