Creating quintiles from dynamic amount of input variables

[SR_MBR]

Dear community,

I need to create a macro which runs for any amount of input variables given, where the input values are grouped into quintiles of a given value. 

For example, I have an simplistic input in the attachment and need to regroup the data into 5 classes so that each new class calculates to a quintile of the sum of all original values (in this example 400/5 = 80). 

In Excel I solve the task by calculating the fraction between the quintile and the original value. If the fraction is below 1 I calculate the remaining rest and add it to the next input value and repeat the process again. If the fraction is above 1, the full input value is used and added to the next input value.

My problem is recreating this workflow in Alteryx. I think I need an iterative macro but am clueless how I can dynamically perform this task.

My initial thought was to calculate the fraction for the quintile and if it is below 1, calculate the remaining value and toss out the already used input value. But I can't create a functioning workflow nor have better idea to perform the task.

Any idea would be most appreciated.

[JoeS]

I think you may be able to use the Tile Tool.

If you transpose the data before, you can then group by the field name in the Tile Tool.

I am not sure if you want to chose Equal Sum or Equal Records based on your description, but I think it's one of those that you need. (I am leaning towards equal sum if you wanted to try that first)

[SR_MBR]

Hi JoeS,

thanks for recommending the Tile Tool. This tool is really helpful, unfortunately it doesnt fully solve my problem.

By using Transpose + Equal Sum it shows me the which records are going into which tile, but doesnt divide the input values to perfectly fit the quintile value. I still need to calculate the fractions to which extent each class fits into a quintile.

[JoeS]

Hi @SR_MBR 

This could be me being a little tired in my lack of understanding.

Is there a way you could upload sample input data with what you have and then output data with what you want?

I am confident it should be possible, just not sure I can picture what you mean.

[SR_MBR]

I attached a sample Input.yxdb to the initial question. The output should contain five variables/classes each with a value of 80 (=Quintile of the sample input data).

In this example the first input value (57) is below 80, so you add it to the next input value and check if the result is now still below or above 80.

57 + 43 = 100 --> It is now over 80 so I have to split the second input value and calculate the remaining value to achieve the quintile. --> (57+43) - 80 = 20 

After that my first quintile is full (57 + 43 - 20) = 80

Now I have 23 left from my first quintile, so I have to add it on the third input value to not lose the remaining value. --> 23 + 56 = 79

My second quintile is not full yet, so I have to add the fourth input value on top and start the splitting process again...

I have to do these steps until all my quintiles are full and therefore have a value of 80.

I hope this clarifies my request. I'm on holiday until Sunday from now on, so I can answer further questions on Monday.

Thanks for your help!

[JoeS]

I think that makes sense.

I have attached a workflow that I believe does what you need.

Enjoy your holiday and let me know if you have any questions when you get back!

[SR_MBR · Accepted solution]

Hey @JoeS ,

your recommendation of the RunningTotal-Tool gave me an idea to solve my problem. Your solution still lacked the exact separation of the input variables into the quintiles. I created an iterative macro for the job. It is not very pretty, but it works 🙂

Thanks for your help!

[SR_MBR]

Attached is my full workflow to create the quintiles and to check the outcome.

[JoeS]

Hi @SR_MBR 

Glad I was able to trigger a thought even if I wasn't able to provide the solution. Nice work!