Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pnn44794
Partner - Specialist
Partner - Specialist

Help Calculating 90th Percentile

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.

 

1 Solution

Accepted Solutions
sunny_talwar

You didn't supply 0.9 here?

Fractile(Actual GTI Days)

May be this

Fractile(Actual GTI Days, 0.9)

View solution in original post

26 Replies
sunny_talwar

Fractile didn't work?

pnn44794
Partner - Specialist
Partner - Specialist
Author

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

You didn't supply 0.9 here?

Fractile(Actual GTI Days)

May be this

Fractile(Actual GTI Days, 0.9)

pnn44794
Partner - Specialist
Partner - Specialist
Author

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

pnn44794
Partner - Specialist
Partner - Specialist
Author

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
Partner - Specialist
Partner - Specialist
Author

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
Partner - Specialist
Partner - Specialist
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
pnn44794
Partner - Specialist
Partner - Specialist
Author

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