Sum, Lookup Mapping

Question

Hi,

Still in the initial learning phase.Was wondering about Sum, Lookup function. When it comes to mapping, any line item in the source module, target module, or any other sys module can be used?

Thanks

bdeaton · Accepted Answer

Yes, you can reference a line item anywhere in your model in a SUM or LOOKUP formula. The key will just be in the dimensions of that line item.

With a SUM formula, you are looking for the mapping line item to include one or more dimensions that are a part of your source data line item and to be formatted by a dimension of your target line item.

With a LOOKUP formula, you are looking for the mapping line item to be dimensioned by a dimension that is a part of your target data line item and formatted by a dimension of your source line item.

Here is an example of a common type of setup that I would use:

Start with two lists, Region & Salesperson. You have a mapping line item in a system module that is dimensioned by Region and formatted as Salesperson. You then select a salesperson for every region (manually or via a formula, it doesn't matter).

Then say you have source data that represents sales by Region. You want to compute sales by Salesperson. So your source is a line item dimensioned by Region and your target is a line item dimension by Salesperson. You can use a SUM statement in the target line item dimensioned by Salesperson and reference the mapping line item above to sum up the sales (from the source line item) by Salesperson.

With the mapping being contained in a system module, I can reference it many times without having to create that same line item & logic in every module where I'm building a formula. Sometimes we still end up with the mapping line item being contained in the source or target module, but if it is something that can be reused elsewhere I recommend leaving the mapping in a system module (I usually create a mapping module for each key list and just add new line items as I build and need new things).