Expand my Community achievements bar.

Join us LIVE in San Francisco on November 14th for Experience Makers The Skill Exchange. Don't miss out on this free learning event!

Filtering to a percentage of issues with a specific form response

Avatar

Level 2

I don't even know if this is possible and I hope I can explain it well.

 

I am trying to create a report to audit documents approved by different people. We are required in our industry to audit 20% of documents submitted the previous month by each of our approvers to ensure they are meeting requirements and give them extra training as needed. 

 

let's say in the previous month, I approved 10 documents.

Doc Num | Approver | Approved Date

1 | Eliza Barry | 1/1/2024

2 | Eliza Barry | 1/2/2024

3 | Eliza Barry | 1/3/2024

4 | Eliza Barry | 1/4/2024

5 | Eliza Barry | 1/5/2024

6 | Eliza Barry | 1/6/2024

7 | Eliza Barry | 1/7/2024

8 | Eliza Barry | 1/8/2024

9 | Eliza Barry | 1/9/2024

10 | Eliza Barry | 1/10/2024

 

I would like to get the first 2 rows in my report, representing a 20% sample

Doc Num | Approver | Approved Date

1 | Eliza Barry | 1/1/2024

2 | Eliza Barry | 1/2/2024

 

bonus points if it can be random

even more bonus points if the count of total submission in the period as a column as well:

Doc Num | Approver | Approved Date | Submission in Pd

1 | Eliza Barry | 1/1/2024 | 10

2 | Eliza Barry | 1/2/2024 | 10

 

 

I'm grateful for any help!

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

4 Replies

Avatar

Level 5

Hi @ElizaBa 

 

The only way you can do is by using Fusion. There is no such provision to show "random 20% of rows". There's also no way to display total count of a set that includes items (the other 80%) not displayed on the report.

Avatar

Community Advisor

 

Hi @ElizaBa,

 

I have an idea, but need to do a bit of experimenting to confirm it will meet your requirements.

 

I will post an update (either way) at my earliest opportunity.

 

Regards,

Doug

Avatar

Community Advisor

 

Yes I did @ElizaBa,

 

Almost every object within Workfront uses an ID as its primary key, and the most important ones also have a unique Reference Number, too. The latter are numeric, so when it comes to random samples, my idea is to filter on the last digit of the Reference Number as a proxy for randomness. In your case, you could use LEFT(ReferenceNumber,1) < 2, which would be 20%, statistically speaking; or if you want to really go crazy, you could look adjust it to look for 6 or 9, and so forth.

 

Although I believe the math works, when I tried to figure out a way to build a native filtering through the UI, I wasn't able to figure out a way to do the "LEFT" part, although it's possible I'm overlooking a trick. In the meantime, though, what you could do instead is add a custom form that does that part in a Calculated Parameter (e.g. RefNumLastDigit), then simply use that parameter for the filter.

 

Have fun, and I'm interested to hear how you make out.

 

Regards,

Doug

 

P.S. another option would be to use the "last digit of the minute of the EntryDate time to control the filter, if you prefer. The ID's are hex based, so getting 20% would take a bit more math