Is it possible to get a set number of records to meet a predetermined sum?

[gmcaleavey]

Example I require 178 rows to total up to 9280 (however this may change ie 200 rows to total up to 10,000 or 152 to total up to 8,826).

I am trying to build 'dummy transaction data' that meets requirements similar to the above question. 

These rows cannot be the same i.e 9280/178 = 52.13 therefore every row would be 52.13 

No rows can be 0.

Ideally the rows will contain whole numbers or be rounded to 2 decimal places i.e. 10 or 10.12

I tried an Macro similar to the one found on the link below but this is taking to long, can it be done without a macro?. 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/random-numbers-which-should-sum-up-to-...

Excuse me if this is a simple problem however I am new to alteryx and am unsure how to figure this out.

Thank you in advance.

[atcodedog05]

Hi @gmcaleavey 

Alteryx running total should help you.

https://help.alteryx.com/current/designer/running-total-tool

[atcodedog05]

Hi @gmcaleavey 

Here is how you can do it.

Workflow:

1. Using running total on value.

2. Filter running total is less than the total required limit. Here I am checking number of rows sum up to 6.

Hope this helps 🙂

[gmcaleavey]

Hi @atcodedog05 

Thanks for the speedy reply.

The problem is I need the specific number of records to add to the predetermined sum e.g. 178 records to total up to 9280.

if I needed 5 records to total up to 10  it could be shown like below

record ID Value   Running Total

1              1          1

2               2         3

3               4         7

4               1         8

5               2         10

so sum is 10 and count is 5

Or

record ID Value  Running Total

1              2         1

2              2         4

3              3         7

4              2         9

5              1         10

Sum is 10 and count is 5

Your example gets to 6 in 3 records. In my problem the count of the records and sum is predetermined. I need the record count to be 178 and the sum of the values to be 9280.

If I am missing your point I do apologise. I hope this better explains my challenge! 🙂 thanks again

[markcurry]

Hi @gmcaleavey , this is an interesting one.  I attached a workflow with 2 options for your 178 rows totalling up to 9280. 

The first way, takes 177/178 records and gives them a random number between 1 and 53 (so all on the low side of the average).  Then it totals all these together, so your 178th will simple be the value that needs to be added to the total to give you 9280.  So the last number will always be a much bigger number than the rest.  Not sure if that's an issue, if you can have negative numbers you could give it a higher random number range.

Then 2nd method is similar, it gives all the 178 numbers a random number between 1.00 and 53.00, then calculates the total that's missing to bring it to the 9280, divides that by 178 and adds it to random value.  Because you're using 2 decimal places, there will be rounding issue, so I've calculated that, and I add\remove that to the 1st record.

I hope that helps, or points you in the right direction.  You could easily change it into a batch macro for your other options.

[danilang]

Hi @gmcaleavey 

Do you have any criteria that need to be applied to your input records other than they can't all be the same value? Do the values have a set range?  Are they normally distributed? Can they be negative?

Dan

[gmcaleavey]

Thanks for the replies,

Mark that seems to work however my only problem is that in my case there may be a single transaction (value) over 53. It needs to be distributed not so tightly around the mean with room for outliers. However your approach may be the most feasible approach so thank you.

Dan, there is no theoretical range that the values must be within. There can not be negative values. Ideally the spread wont be tight around the mean. As I am trying to generate dummy transaction real life data rarely, if ever, follows a perfect normal distribution i.e the average transaction might be 50 but the account holder will make and receive payments much higher/lower than this.

I hope this helps, I appreciate the support so far. I am relatively new to the world of data analytics and Alteryx so bear with me if I am not explaining myself clearly enough. Thanks,

G

[danilang]

Hi @gmcaleavey 

Here's a workflow that gives what your looking for.  

It starts by building an initial random dataset with non-negative values.  The first summarize tool calculates the initial total. This value is appended to the initial set of values and the next formula tool distributes the excess/deficit among all the records.  At this point the total is correct, but there may be negative values.  These values are split and the summarize tool gives us the total number of positive values that need to be adjusted downwards by 1 to compensate for setting all the negative values to 0.     

Dan  

[gmcaleavey]

Hi Dan,

Thanks for your help 🙂

I ran the workflow a few times and sometimes there are no negatives and everything works fine and other times there are negative values and this is reflected in the final values, I updated the formula  tool for the negatives (as it is an empty tool on your workflow) to Value = 0 then there are zeros reflected in the final values and the sum is over by how many negative records occured. The formula tool adjust positive values downwards does not seem to be doing its job?

I have also noticed that the workflow you provided contains an extra filter tool to that in the image. 

Gary

[DAStudent123]

Thanks Dan, your workflow works great except I have the same problem as @gmcaleavey with the negatives. Would you help as well please. I believe the top right "adjust positive..." formula tool doesn't work as it doesn't adjust the positive values to compensate the negative values.