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.

[pnn44794]

Hello Sunny:

You are correct.  I did indeed say that your expression appears to be working.  However, before I said that, I said it looks like I'm not getting the correct results with it as the numbers should have been lower.  It's that response that I believe Anil responded to (Anil can say one way or the other).  He then started looking at my original attempt and started trying to figure out how to correctly code it since I was unable to.

When I said it looks like your solution is working in a later post, I had determined that I had a problem with the data in that I was not pulling all of it and thus the numbers were skewed high (and incorrect).  I got a more up to date pull of the data and it looks like your solution is working, though I'm still not 100% positive.  I really need to get a good, clean pull of ALL the data.  I'm only able to pull part of it right now, but more than when I originally responded.  I'm dependent though, on others to check and then correct any data issues and they are not working this weekend.  So, Anil's effort may be worthwhile in the event I get a full and correct dataset to test with.

Make sense, my friend?

[pnn44794]

Hello Anil:

Let me explain Host Name.  That column is used to count the number of orders.  So, 1 Host Name = 1 Order, 2 Host Name's = 2 Orders and so on.  Does that help?  And you're right.  It is a text field.

[sunny_talwar]

It does ... I think I would rather wait to get cleaner data before getting to any conclusions or trying anything else. Because all this effort from all 3 (or any other) might just go in vain. Better to wait for when we can actually confirm... what do you guys think? I am okay if you guys want to continue looking, but I would rather wait until we know that there are not going to be any issue with the data

[pnn44794]

Good point Sunny. Since my last reply, I happened to catch one of the people online who can validate data accuracy / completeness. They said the data is there minus two columns. Those two columns would not figure into what I’m trying to do. So, I will do my checks and let you guys know. I appreciate both of your patience and understanding.

[pnn44794]

Hello Anil:

I'm going to mark Sunny's answer as correct since it appears to be working correctly once I got a decent dataset pulled down.  I'll mark your's as helpful.  Please note, no offense is meant or intended.  Sunny simply responded first.

With that said, if you'd like to continue working this with the other expression, I'd be happy to do so.  Thank you very much for your assistance.  As always, it is appreciated.

[sunny_talwar]

Perry -

You are free to do what you want, but I would not close this until the final resolution has been received.

Best,

Sunny

[pnn44794]

Understood and I appreciate the advice. I truly do. I received some info that upset me though and I don’t want folks to continue working on it. If I need additional help, I’ll open a new discussion. Your solution does work with the current dataset. Anil’s efforts are greatly appreciated (as are yours) and I gave him the option of continuing or stopping. Whatever he decides is fine with me. I’ve marked his efforts as helpful and they are appreciated. I tagged, as you know, your answer as correct.