Decile fuction

[Rajesh31]

Hi , Can some one help me with below one 

I tried creating a column for decile bucket using following code in load editor

if (revenue_amount<=Fractile(sum(revenue_amount),0.1,'D1',

if (revenue_amount<=Fractile(sum(revenue_amount),0.2,'D2',

if (revenue_amount<=Fractile(sum(revenue_amount),0.3,'D3',

if (revenue_amount<=Fractile(sum(revenue_amount),0.4,'D4',

if (revenue_amount<=Fractile(sum(revenue_amount),0.5,'D5',

if (revenue_amount<=Fractile(sum(revenue_amount),0.6,'D6',

if (revenue_amount<=Fractile(sum(revenue_amount),0.7,'D7',

if (revenue_amount<=Fractile(sum(revenue_amount),0.8,'D8',

if (revenue_amount<=Fractile(sum(revenue_amount),0.9,'D9','D10')))))))))) as Decile_bucket

It is throwing an error of nested aggregration not allowed while loading the data 

What Iam trying to acheive is to create an equaly distributed decile bucket of Revenue_amount as dimension , I seek your guidance

@sunny_talwar @swuehl 

[David_Friend]

@Rajesh31 did you already come across this thread on Community: 

https://community.qlik.com/t5/QlikView-App-Dev/nested-aggregation-not-allowed/td-p/634976

[vinieme12]

you are missing closing brackets for the Fractile() 

also where exactly are you try to place this measure ?? in Measures or Dimensions?

if (revenue_amount<=Fractile(sum(revenue_amount),0.1  ),'D1',

if (revenue_amount<=Fractile(sum(revenue_amount),0.2 ),'D2',

if (revenue_amount<=Fractile(sum(revenue_amount),0.3  ) ,'D3',

if (revenue_amount<=Fractile(sum(revenue_amount),0.4 ),'D4',

if (revenue_amount<=Fractile(sum(revenue_amount),0.5 ) ,'D5',

if (revenue_amount<=Fractile(sum(revenue_amount),0.6 ) ,'D6',

if (revenue_amount<=Fractile(sum(revenue_amount),0.7 ),'D7',

if (revenue_amount<=Fractile(sum(revenue_amount),0.8,'D8',

if (revenue_amount<=Fractile(sum(revenue_amount),0.9,'D9','D10')))))))))) as Decile_bucket

[Rajesh31]

Iam trying to create it as a dimension

[Rajesh31]

@David_Friend , I have gone throught it ,but not able to figure it out

[vinieme12]

you then need to wrap this entire expression in AGGR() 

= AGGR(    

if (revenue_amount<=Fractile(sum(revenue_amount),0.1  ),'D1',

if (revenue_amount<=Fractile(sum(revenue_amount),0.2 ),'D2',

if (revenue_amount<=Fractile(sum(revenue_amount),0.3  ) ,'D3',    .................... )))   , FieldNAme1,FieldNAme2 )

 FieldNAme1,FieldNAme2  with the actual field you need to aggregate sum(revenue) by

[Rajesh31]

@vinieme12 I tried closing the bracket but its still throwing the same error , Iam trying to create a dimension on it

[Rajesh31]

@Vini Aggr function is not working in data load editor, I tried creating a variable with it

= AGGR(

if (revenue_amount<=Fractile(sum(revenue_amount),0.1  ),'D1',

if (revenue_amount<=Fractile(sum(revenue_amount),0.2 ),'D2',

if (revenue_amount<=Fractile(sum(revenue_amount),0.3  ) ,'D3',    .................... )))   , Product )

when I check the table output its giving D10 for all id values

[vinieme12]

completely missed that you are trying this is data load editor,

you need to calculate sum(revenue_amount)  in a different table and then join it to the main table for this comparison to be done

tempFact:

load  keyfield,somefield,revenue

From XyzDource;

Left Join(tempFact)

somefield,

sum(Revenue) as totalrevenue

Resident tempFact;

Fact:

keyfield,somefield,revenue,totalrevenue,   if(  revenue < fractile(totalrevenue,0.1),'D1',    ____ and so on

Resident tempFact;

Drop table tempFact;

[Rajesh31]

@vinieme12 

it says invalid expression  here

Left Join(tempFact)

somefield,

sum(Revenue) as totalrevenue

Resident tempFact;