topic Re: Help Calculating 90th Percentile in QlikView

Help Calculating 90th Percentile

pnn44794 — Fri, 11 Oct 2019 19:53:02 GMT

Hello:

I would appreciate any help in being able to calculate the 90th percentile for the attached sample data and have it in the attached intended output format (QV Pivot Table).

I need the 90th percentile for each Product End Build Type (DB Build, OS Build, WEB Build) for each Service New type (Bespoke, PSI, VSI) by Average of Actual Days. Does that make sense?

Here's some additional information that may be helpful in regards to the sample data.

Product End Build Type is a calculated dimension using the following calculation (open to a better way of doing it)

=Pick(WildMatch([Product Build End Type], 'VSI (DB)', 'VSI OS Build Only', 'VSI (Web)', 'PSI OS Build Only', 'PSI (DB)', 'PSI (Web)', 'Bespoke (DB)', 'Bespoke OS Build Only', '*'),
'DB Build', 'OS Build Only', 'WEB Build', 'OS Build Only', 'DB Build', 'WEB Build', 'DB Build', 'OS Build Only', [Product Build End Type])

# of Orders is a simple expression of Count(Host Name)

Here's some information regarding the Intended Output (QV Pivot Table).

Again, Product End Build Type is a calculated dimension using the following calculation

# of Orders is expression Count(Host Name)
Average of Actual GTI Days is expression (Avg([Actual GTI Days])
For the 90th percentile, I've tried the following which has a syntax error and obviously does not work

If([Product Build End Type] like 'OS Build%', Fractile(Aggr(Sum(Actual, [Host Name]),0.9)))

As always, thanks in advance for any and all help.

Re: Help Calculating 90th Percentile

sunny_talwar — Fri, 19 Jan 2018 20:29:30 GMT

Fractile didn't work?

Re: Help Calculating 90th Percentile

pnn44794 — Fri, 19 Jan 2018 20:43:31 GMT

I tried Fractile(Actual GTI Days) and I get - (dash) in the 90%tile column. Also, Qlikview thinks there is a syntax error with the expression as it says Error in expression and there's a red squiggly line under the last parentheses.

As an FYI, Actual GTI Days is created in the load script with Num(If(IsNull([Business Days End 2 END ( without Approvals )]), [Business Days End 2 END ( with Approvals )], [Business Days End 2 END ( without Approvals )]), '#0') as [Actual GTI Days].

I also tried If([Product Build End Type] like 'OS Build%', Fractile(Aggr(Sum(Actual, [Host Name]),0.9))), but it doesn't work and Qlikview thinks there is a syntax error with it as well.

Re: Help Calculating 90th Percentile

sunny_talwar — Fri, 19 Jan 2018 20:45:18 GMT

You didn't supply 0.9 here?

Fractile(Actual GTI Days)

May be this

Fractile(Actual GTI Days, 0.9)

Re: Help Calculating 90th Percentile

pnn44794 — Fri, 19 Jan 2018 20:48:21 GMT

Darn it. LOL. Let me try that. Not sure how I forgot that, but I did.

Re: Help Calculating 90th Percentile

pnn44794 — Fri, 19 Jan 2018 21:03:08 GMT

Ok. I feel dumb. I added the 0.9 and that eliminates the syntax error. Thank you for catching that. Sometimes you just need another set of eyes to see the obvious.

With that said, I don't think I'm getting the right results. For example, for OS Build Only and Service New value of VSI, I was expecting to see something like 32.88 for the 90%tile value and instead I have 142.97.

Re: Help Calculating 90th Percentile

pnn44794 — Fri, 19 Jan 2018 21:06:18 GMT

Clarification. I get 142.97 just for December OS Build Only and Service New = VSI.

For the whole dataset, I get 56.34 for VSI and 89.21 for PSI and I'm expecting to see lower numbers.

Re: Help Calculating 90th Percentile

pnn44794 — Sat, 20 Jan 2018 01:12:14 GMT

Any idea on why I may not be getting the right percentile #? If you haven't already, please see my other posts below. I may not have responded in a manner that you would see that I have.

Re: Help Calculating 90th Percentile

Anil_Babu_Samineni — Sat, 20 Jan 2018 08:54:31 GMT

I am starting from here If([Product Build End Type] like 'OS Build%', Fractile(Aggr(Sum(Actual, [Host Name]),0.9)))

Can you try without Aggr() here? like

If([Product Build End Type] like 'OS Build%', Fractile(Sum([Actual, [Host Name]]),0.9))

Re: Help Calculating 90th Percentile

pnn44794 — Sat, 20 Jan 2018 21:18:22 GMT

Hello Anil:

How are you? Thanks for the reply. I tried it, but Qlikview thinks there's an error in the expression. There's a squiggly line under the last parentheses (see below). I tried different positions of the parentheses, but no luck. They seem to match up. Are we maybe missing a parameter? Also, as an FYI, I changed fieldname from Actual to Actual GTI Days. I also changed 'OS Build %' to '%Build%' as I have two other build types to account for as well. I tried your expression as is too with no luck (Error in expression).

If([Product Build End Type] like '%Build%', Fractile(Sum([Actual GTI Days], [Host Name]),0.9))

Re: Help Calculating 90th Percentile

Anil_Babu_Samineni — Sun, 21 Jan 2018 01:58:45 GMT

Thus [Actual GTI Days] and [Host Name] are different Fields? Perhaps this?

If([Product Build End Type] like '%Build%', Fractile(RangeSum([Actual GTI Days], [Host Name]),0.9))

Re: Help Calculating 90th Percentile

pnn44794 — Sun, 21 Jan 2018 02:09:39 GMT

Hello Anil:

Yes, both are different fields.

I tried your expression and it does not return anything. However, it now appears that there was a problem with the data source I was pulling from. I have more records and I was thinking it was a bit odd that the row count had gone down but it could have been because of the new year turn over.

With that said, Sunny's syntax correction above (Fractile(Actual GTI Days, 0.9)) appears to be doing the trick and I'm getting what I was expecting to get.

However, if you want to keep trying with your solution, I'm willing to keep trying.

Re: Help Calculating 90th Percentile

Anil_Babu_Samineni — Sun, 21 Jan 2018 02:11:59 GMT

When you say there is problem with scram, I think you have to work from that. Anyway, Would u able to share sample to investigate?

Re: Help Calculating 90th Percentile

pnn44794 — Fri, 11 Oct 2019 19:55:11 GMT

Hello Anil:

scram? Not sure I follow you. In any event, are you able to access the sample data I posted when I first created this thread or do you need me to post it again? Actually, I'll just go ahead and post it again just incase.

Re: Help Calculating 90th Percentile

Anil_Babu_Samineni — Sun, 21 Jan 2018 03:10:51 GMT

Great, But in attachment there is no [Host Name] field, Is that field comes from another table?

Re: Help Calculating 90th Percentile

pnn44794 — Sun, 21 Jan 2018 03:13:15 GMT

Shoot. No it comes from the same table. My fault and my apologies. I'll update the sample data to include it. Am I missing anything else? Again, my apologies.

Re: Help Calculating 90th Percentile

Anil_Babu_Samineni — Sun, 21 Jan 2018 03:19:30 GMT

No, that is it i believe

Re: Help Calculating 90th Percentile

pnn44794 — Fri, 11 Oct 2019 19:56:32 GMT

Here you go. Sorry about that. As an FYI, I'll be logging off soon, so if you need something and I don't respond right away, that's probably why, but I will respond tomorrow. Thanks for your help.

Re: Help Calculating 90th Percentile

Anil_Babu_Samineni — Sun, 21 Jan 2018 05:25:47 GMT

In that second field there is something Text involved, Not sure what you are doing with this?

Sum([Actual GTI Days], [Host Name])

stalwar1‌ Do you understand his intend?

Re: Help Calculating 90th Percentile

sunny_talwar — Sun, 21 Jan 2018 15:06:34 GMT

At one time, I saw pnn44794‌ saying that one of my expression worked.... If what I gave worked, what exactly are we trying to resolve here? I might have missed another question... but can one of you guide me

Best,

Sunny