Creating a sample based on several conditions

Question

Hi there,

I have a data set that looks like this...

Zone       Units         Rank

2             12,000       1.75

2              550            3.41

3              750           2.32

4              100           7.54

What I want to do is grab a percentage of the units according to the zone up until a certain total number. Example I need 50% of the units to be from zone 2 but the total units for all zones should not exceed 1 million. But I also want it to grab the units based on rank, the lower ranking lines should be added to the sample set first.

Thank you for your time,

Natalia

RobertOdera · Answer

Hi, @nwatzlaf

Thanks for the question!

Please like + mark as an acceptable solution if this works for you.

Based on your brief, I understood the following:

* You have multiple Zone, but Zone 2 has allocation over-weight (50% of Total) on the entire selection set.
* Selection into the sample set is based on units thresholds (Zone 2 50% of Total, Total not greater than 1 MIL units, and the whole selection set has predetermined distinct Rank per row)

Your sample file does not cover all your criteria, so I generated an alternative sample to work through your actual use-case mechanics/ criteria.

A couple of things:

- You have an absolute number for Total Units (1 Mil), but a percentage based allocation weighting = cumulative total Zone 2 units will not always add up to exactly 50% or 500,000 units. At a tally < 500,000 units, the next incremental record may push the cumulative > 500,000, so it will be omitted.

- I've seeded prioritization + threshold indicators + qualification = forced chronological approach to sampling selection (First Zone 2, then all other Zones) versus a fully randomized treatment

- I've commented on the workflow, but please let me know if it's still as clear as mud 🙂 and if you need anything else.

The workflow is attached.

Cheers!

For_nwatziaf.yxmd

OllieClarke · Answer

Hey @nwatzlaf here's a simple workflow that uses a multi-row formula and filter to get what you want

Here's the total units per zone (also as a percentage) so you can verify

Hope that helps, Ollie

sample.yxmd