Decile analysis. How does fractile work?

[prasadmundewadi]

I have data like this:

Opportunity          Value

O1                         100
O2                          50

O3                          700

.

..

.

O1000                   200

What I want to do is sort the Opportunities by Value and then create 10 sets with top 100 oppty and the total value (value) then next 100 and their total value and so on.

I tried using Fractile but not able to get it.

[sunny_talwar]

What have you tried? Can you share that

[prasadmundewadi]

My sample Set of 30 values:

T4:

load * Inline [

Opp , Val

O1 , 10

O2 , 23

O3 , 45

O4 , 567

O5 , 23

O6 , 35

O7 , 567

O8 , 458

O9 , 6

O10 , 4

O11 , 67

O12 , 6

O13 , 67

O14 , 345

O15 , 22

O16 , 445

O17 , 67

O18 , 643

O19 , 9786

O20 , 56

O21 , 56

O22 , 43

O23 , 3442

O24 , 1234

O25 , 342

O26 , 455

O27 , 7698

O28 , 7909

O29 , 789

O30 , 6765];

I tried creating a Pivot table with Dimension as

=Aggr(

if(Sum(Val) <= fractile(TOTAL Aggr(Sum(Val), Opp), 0.1), 10,

if(Sum(Val) <= fractile(TOTAL Aggr(Sum(Val), Opp), 0.2), 9,

if(Sum(Val) <= fractile(TOTAL Aggr(Sum(Val), Opp), 0.3), 8,

if(Sum(Val) <= fractile(TOTAL Aggr(Sum(Val), Opp), 0.4), 7,

if(Sum(Val) <= fractile(TOTAL Aggr(Sum(Val), Opp), 0.5), 6,

if(Sum(Val) <= fractile(TOTAL Aggr(Sum(Val), Opp), 0.6), 5,

if(Sum(Val) <= fractile(TOTAL Aggr(Sum(Val), Opp), 0.7), 4,

if(Sum(Val) <= fractile(TOTAL Aggr(Sum(Val), Opp), 0.8), 3,

if(Sum(Val) <= fractile(TOTAL Aggr(Sum(Val), Opp), 0.9), 2, 1))))))))), Opp)

This gives me result like

My problem it it should have done 3 Oppty in each decile but it is doing 4 in some 2 in some and 3 in some. Why?

[sunny_talwar]

May be use this dimension

=Aggr(Ceil(RowNo()/(Count(TOTAL Opp)/10)), (Opp, (=Sum(Val), DESC)))

[Sato-G]

Here's how I tried.

<Loadscript>
data:
LOAD
RowNo() as Oppotunity,
RowNo()*10 as Value
AutoGenerate 500;

<Chart>
Dimension:
=Aggr(Ceil((Rank(Sum(Value))/Count(Total distinct Oppotunity))*10),Oppotunity)