2 Replies Latest reply on Jun 20, 2019 1:11 PM by Syed Raza

# How to create Quintile bins using a calculated field

Hey I've been trying hard to solve a task related to quintile bins.. I've solved Q1 of it but got stuck in Q2 onwards these ones are really really tricky any help would be mean a lot. The details of the tasks are in the attachment.

Question 1: Create a table of 1988 data with RRinc and pop measured for each income decile group and country code, using the mysample = 1 portion of the dataset, with the entire table sorted in order of increasing RRInc, and a new calculated field called running pop that is the cumulative total of the pop field for the current row of the table and all rows before it. Here’s how the output table in below would look like - the first three entries. I need the data table as a CSV.

Question 2: Group the result of Question 1 into quintiles, which are bins representing roughly 5% of the world population according to annual income. For example, using Tableau table calculation could be: MIN(19,INT(20.0*RUNNING_SUM(SUM([Pop]))/TOTAL(SUM([Pop])))) which simply divided the running sum of the population with the total sum of the population (the last row's running sum entry), and quantized that ratio to an integer from 0 to 19. The result of step 2 should be a table of 20 aggregated values corresponding to the average RRinc of the Question 1 rows that share the same quintile value. Here’s how the output table would look like in below - the first three entries. I need the data table as a CSV.

Question 3: Compute the above steps in questions 1 & 2  for the 2008 year, then compute the percentage difference between the 2008 quintiles and the corresponding 1988 quintiles as Growth[i] = (2008Quintile[i] - 1988Quintile[i])/1988Quntile[i] for each of the 20 values of i corresponding to each quintile in your 1988 and 2008 quintile tables. Output should like this - the first three entries is as below. I need the data table as a CSV.

• ###### 1. Re: How to create Quintile bins using a calculated field

Have you taken a look at Richard Leeke's work?  Table Calculation: Quantile (version 2)

• ###### 2. Re: How to create Quintile bins using a calculated field

Thanks for the help but I tried it and couldn't make it work on getting the desired output.