High value scoping selections

[Jaloy_1973]

Hi Community,

I'm just starting with Alteryx, and I'm struggling with a workflow. If anyone can help me, it would be appreciated.

I'm tring to make a workflow to sample a population using high value scoping. That is, we order the population from highest to lowest, and select the items whose cumulative sum is the % we define.

In the attached case, I want to select those items that represent at least the 50% of total population (Column  "RunTot_% TOTAL"). 

I was using a filter (see example), but the output gives me the highest items until the 48.75% of the total population, so it would be missing, in this case, another item to reach the minimum of 50% (final scope 50.58%).

I've tried another filters but without success.

Any idea, or suggestion?

Thanks,

Jordi

[JamesCharnley · Accepted solution]

Hi @Jaloy_1973 

You can leverage the multi-row formula to look up and down rows. In this case, I'm using it to tag rows that are either less than 50%, or it's the first row where the previous row is <=50 and the value is >=50, so it'll also take that row.

[caltang]

Wait... why would you take 50.58%? Isn't that above 50% already?

[Jaloy_1973 · Accepted solution]

Thanks a lot James.

It works!!!!

Best,

Jordi

[Jaloy_1973]

Because my minimum sample size must be minumum 50% of total population.

50.58% is slightly over, but 48.75% is below and does not meet the criterion.

Thanks,

Jordi

[caltang · Accepted solution]

Okay - I get you.

You can try this as well:

The concept retains your filter, but adds one more row - the next row immediately after and nearest to your max closest of <=50%. Hope this helps @Jaloy_1973 !

[geraldo · Accepted solution]

@Jaloy_1973 

In the filer tool I would put the Floor() function

[Jaloy_1973]

Thanks Geraldo for the idea.

[Jaloy_1973]

Thanks a lot Catlang!!!

Jordi