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.

[Anil_Babu_Samineni]

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

[pnn44794]

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. 

[Anil_Babu_Samineni]

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?

[pnn44794]

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.

[Anil_Babu_Samineni]

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

[pnn44794]

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.

[Anil_Babu_Samineni]

No, that is it i believe

[pnn44794]

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.

[Anil_Babu_Samineni]

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?

[sunny_talwar]

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