Discussions

View Only

Summary Report Based on Similar Values in Two Fields in Same Table

• 1.  Summary Report Based on Similar Values in Two Fields in Same Table

Posted 24 days ago
Hello,

I am trying to create a summary report for our sales reps that will show deals they are the sole sales person on and deals that the split with another rep, to help them forecast their sales. Depending on the deal, they may share the forecast number, and we'd like to display the split amount for better forecasting per rep.

I have two User fields: [Sales Rep] and [Sales Rep 2], that the sales rep could be in, depending on if they are the main rep or secondary in splitting the deal. I have another field that calculates the split amount (usually 50/50 of the full deal amount).

Example: Reps Smith, Wilson, and Jones are splitting deals. Deal 1, Smith is the main rep with Wilson as the 2nd rep, Deal 2, Wilson is the main rep with Jones as the 2nd rep. In the summary report, Wilson would show all sales opps where he is listed in either [Sale Rep] or [Sales Rep 2] field, which would include both split deals.

1. Is there a way to create a summary report to show all deals for a sales rep whether they are in [Sales Rep] OR [Sales Rep 2] field?
2. Will a summary report show the same record twice in the report? Per the example, would Deal 1 be calculated under Smith & Wilson and Deal 2 under Wilson & Jones?

Thank you!

------------------------------
Jenni Murray
Detroit, MI
------------------------------

• 2.  RE: Summary Report Based on Similar Values in Two Fields in Same Table

Posted 24 days ago
yes we can get there.

Two elements to the solution.

1. make a formula field that calculates to the full amount of there is only 1 Rep and to 50% of the amount if there is a Shared sale.  use that field in our summary report.

Then make a field of type Formula multi-select called [Rep DF]  (DF means Dynamic Filter.)

Split(UserListToNames(ToUserList([User 1], [User 2])))

Then you should be able to have a summary report and  use the Dynamic filter to get one rep's totals at a time without needing to have a whole bunch of separate reports by rep

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------