Complex Sample Selection row as % of total for given group

C_Allen

Apologies if the question seems simple.  This is my first workflow, and while I have sorted most of my own questions, this solution eludes me;  I would be be grateful for your thoughts. 

Setup:
* I have a large dataset, I have done a series of manipulation on that data to endup with an Entity,  Row value (RV), and Sum of row value by entity (SRV)

* Original dataset had 1-1000 rows per entity

* Further reduced my target data by sorting by descending Row Value and taking a sample of the top 4 rows

The Goal:
* I only want to identify the top 75% of rows by RV as a % of SRV

Method that has had the most results: 
* Created a Running total of RV (RTRV)
* Calculated RTRV as a percentage of SRV. 
* Created a flag  column with the logic with the logic:  if RTRV_pct_SRV < .75, "Y", else "N" endif

The issue is that this will flag while less than.  But I really need while less than and the next item, the one that brings it over 75%, or if the RTRV_pct_SRV never reaches 75%, then i need all 4 values. 

jrgo

Hi @C_Allen 

If I understood your explanation correctly, using the Multi-Row formula tool should get you what you need.

it's contains the same IF conditions you're already using, but using this tool allows you to look at records before or after (i.e. window/table calculation). It includes a secondary condition (ELSEIF) that checks if the current record is over 75 and the previous record was under 75 then Y.

The subsequent rows will all then fall to the ELSE value since the current record and previous record will NOT meet the first two IF conditions.

Hope this helps!

Jimmy

C_Allen.yxmd