Help Calculating 90th Percentile

[pnn44794]

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

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

[sunny_talwar]

Fractile didn't work?

[pnn44794]

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.

[sunny_talwar · Accepted solution]

You didn't supply 0.9 here?

Fractile(Actual GTI Days)

May be this

Fractile(Actual GTI Days, 0.9)

[pnn44794]

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

[pnn44794]

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.

[pnn44794]

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.

[pnn44794]

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.

[Anil_Babu_Samineni]

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

[pnn44794]

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