I have a report that pulls out a fee credit assigned to a different MD in our company based on engagement number. An MD could be assigned to an assessment and also an implementation. Even though these are two separate engagement names, they can have the same engagement number and therefore different fee credits for each record. In my report, there is an MD that received fee credit for both an assessment and implementation with the same engagement number. When I run the report, it double counts the fee credit and makes it exactly the same for both the assessment and implementation. For example, it will show Eng name: Assessment and Fee Credit $500. Then the next line will be Eng Name: Implementation and Fee Credit $500. When I look in the table, the fee credits are clearly different. When I try to add a filter in my NL formula for engagement name, it won't allow me because engagement name is being populated by an NF formula in a different cell. Any advice on how to make this work?
3 comments
-
Morgan Healey For reference, here is the NL formula currently: (NL("Sum","MD Scorecard","Final Credit","DataSource=",datasource,"Role Emp Num",$F$4,"Eng Number",$F20,"Role","Originator","Quarter End Date",$F$6))
This formula works but it populates a fee credit of $500 when in fact the assessment was $200 and the implementation was $300. If I change it to an NL First formula, it will populate both lines of the assessment and implementation, but the fee credit for both will read $200, which is also incorrect. When I add in engagement name to the formula like below, it returns a blank cell and doesn't work:
(NL("Sum","MD Scorecard","Final Credit","DataSource=",datasource,"Role Emp Num",$F$4,"Eng Number",$F20,"Role","Originator","Quarter End Date",$F$6,"Eng Name",$G20))
For reference, the Engagement name cell G20 has the following formula in it: NF($E20,"Eng Name")
And cell E20 is this formula: =NL("Rows","MD Scorecard",{"Eng Number","Eng Name"},"DataSource=",datasource,"Role Emp Num",$F$4,"Quarter End Date",$F$6,"Outside Practice","''")
-
Harry Lewis Hi Morgan -
You state that "This formula works but it populates a fee credit of $500 when in fact the assessment was $200 and the implementation was $300."
If you look at a record in the MD Scorecard table, how does each record differentiate between Implementation and Assessment?
For example: let's assume that there is a field name Service Type and that field could either be set to "Implementation" or "Assessment". Then, your NL(Sum) function (which you state returns 500) could be split into two functions:
=NL("Sum","MD Scorecard","Final Credit","DataSource=",datasource,"Role Emp Num",$F$4,"Eng Number",$F20,"Role","Originator","Quarter End Date",$F$6,"Service Type","Implementation")=NL("Sum","MD Scorecard","Final Credit","DataSource=",datasource,"Role Emp Num",$F$4,"Eng Number",$F20,"Role","Originator","Quarter End Date",$F$6,"Service Type","Assessment") -
Morgan Healey Thank you, Harry. I ended up using the unique field of Opportunity ID, which was being pulled out of SalesForce, to uniquely identify the Assessment from the Implementation. I appreciate the help.