Find the 95th record by using expression

[#1]

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

[sunny_talwar]

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?

[sunny_talwar · Accepted solution]

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)

[Digvijay_Singh]

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

[#5]

Thank you very much for you help Sunny

[#6]

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

[sunny_talwar]

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

[#8]

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

AJ

[sunny_talwar]

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