Sample based on total

Question

Hi,

Attached the workflow I have created to choose sample transactions for review.   This is based on the data called " CAPWIP Balances" column AP. Generally, the column AP total value will be around $100M or so.  Currently in the attached workflow the sample transactions total is around $10M.

How do I increase the sample to ensure around 90% of the total value of the sample transaction is selected for review. Overall then selected transaction for sampling is around $80M or so that way from the total of $100M at least 85% of the transactions has been selected for review. I wanted to make sure large set of transactions is included for review the sample.

Sample testing workflow package.yxzp

danilang · Answer

Hi @Thiviya

Here's a possible solution.  It's based on the generic way to randomize a list.  First you assign a random number to each item in the list and sort by the random number.

For your case, I first generate the AP totals for each master Project and join this the list of entries.  Then I sort the items by Master Project and then rnd.  The Running Total generates the running total for the randomized entries grouped by Master Project.  The last Formula tool is where I mark all the records where the running total is less than the AP_Total*.85.

You can extend this method by adding extra criteria to the marking phase. For instance, if you always want to sample at least 50% of the records, you can add a count to the initial Summarize tool, a RecordID grouped by Master Project after the Sort tool and modify the Formula tool to be RunTot>=AP_Total*.85 and RecordID >=Sum_Records*.5

Dan

Total Based Sampling.yxzp

aatalai · Answer

@Thiviya does this do the trick some of the numbers might need to be changed in the filter tool and random sample, but it should get to where you want. Let me know how you get on.

Sample testing workflow package.yxzp