Random Sampling Formula

[ekarmanov]

Hello,

Hoping someone can provide guidance/suggestions on how to best handle to following:

I have already performed a number of steps to arrive at my desired final population of records (numbered 1 through 89 in this case) using the Record ID tool.

Now I need to obtain a random sample from this population considering the following:

1. Determine sample size based on the parameter: 10% of population count (89) with a maximum of 25 and a minimum of 5

a. The required sample size should be 9 in this case. This sample size should always be rounded up to the nearest whole integer (e.g., even if 10% of population was 8.1, the required sample size would still be 9). 

b. I can't seem to determine a way for Alteryx to provide a basic RoundUp type formula/functionality like in Excel.

2. Have Alteryx produce a random sample based on the required sample size. 

a. The usual supects Sample and Random % Sample tools don't seem to have a way of inputing required sample size automatically.

Thank you,

Eugene

[chris_love · Accepted solution]

Hi Eugene

Here's the steps I go through.

1. Use a Count Records Tool to Count the number of records

2. Append this to the original data using the "Append" Tool

3. using Formula Tool: Multiply the total by 0.1 and use the Ceil()
function to Round Up.

4. Use and IF statement to apply the minimum and maximum - this is your
Sample Size

5. Use Formula Tool to Create a Rand() number

6. Use Sort Tool to apply a sort by the random number

7. Attach a Record ID using Record ID tool

8. Use Filter to find all rows where RocordID <= Sample Size

Happy to supply a module but since you are new I thought you might like to
try this yourself.

Chris

[MarqueeCrew · Accepted solution]

@chris_love and I were very close.  I just completed the exercise and created a workflow for you.  We approached it in a similar way.

[ekarmanov]

Thank you, both, for the quick and excellent solutions.

I appreciated the opportunity to learn doing this on my own as well as seeing a clearly laid out solution.

The only (minor) difference that I was initially considering for the sample size calculation formula was to use a combination of Ceil and Bound formulas (i.e., Ceil(Bound(0.1*[Count], 5, 25)).

Best Regards,

Eugene

[chris_love]

Thanks Eugene - I didn't even know about the Bound function!

[marlline]

I just realized that Random Sample Tool did not give me random sample at all. When I enter # of records, it might just give me first #. Thank you so much for sharing!