How to map a line item into a dimension

Hello,

 

I have a module with dimensions Employee and Time (Months). The module contains a list formatted line item "Shop Number" (the shop the employee works in), and number formatted line item "hours worked" (the number of hours that employee worked per month).

 

I want to bring this data into a second module with dimensions Time (year) and Shop Number. l would effectively like to see how many hours are worked per shop, using the data input into the first module. 

 

Would anyone be able to advise me on the correct formula for this? 

 

Thansk in advance!

Best Answer

  • @Tom_H

    Hi

     

    Trying to move from one dimension to another AND transform from month to year is tricky, but there is a simple way to achieve this with an additional "staging": module.

    1. Let's first move the data from Employee to Shop keeping the time dimension the same.

    So we create a Shop Hours by month module

    Let's now look at the employees to shop relationship

    The source is dimensioned by employee and the target is dimensioned by shop

    But, we do have the relationship mapped out in your "shop number" line item

    So imagine if we could put that relationship inbetween the source (Employee) and the target (Shop)

    The relationship is a Many:1 relationship.  Many employees sum up to 1 shop.  That is the clue!, the formula is the SUM formula

    the result formula should be:

    Employee Hours.Hours[SUM:Employee Hours.Shop Number]

     

    What I would say, is that if the shop number for each employee doesn't vary by month, it is much better to hold that data in a separate module dimensioned only by Employee (e.g. Employee details)

    The formula would then be:

    Employee Hours.Hours[SUM:Employee details.Shop Number]

     

    2. Ok, now that we have the data by shop and month

    We can now link this directly into the target 'Shop Hours by Year' module, just by referencing the staging module

    The formula would be:

    Shop hours by Month.Hours

     

    You could dispense with this "yearly" module and just show the annual totals of the 'shop hours by month' is needed

     

    Good luck.

    There is more information here:

    https://community.anaplan.com/t5/Best-Practices/Converting-data-between-line-items-with-different-lists/m-p/37976#M1741

     

    David

Answers

  • Assuming you have Module A with dimensions Employee and Time, and Module B with dimensions Shop Number and Time. For the new line item "B.Hours Wokred in a Shop" Could you try formula:

    B.Hours Wokred in a Shop = A.hours worked [sum: A.Shop Number].

  • Thanks for your soloution David. 

     

    I can see that my problem was trying to use SUM as per below whilst also trying to go from a module with Months to a module with Years. 

     

    Is it a general principle that you are unable to roll-up time whilst also using formulae to convert dimensions?

  • @Tom_H

    On this, and referring to the last point, if you have a fixed relationship between Employee and Shop (using the module as discussed), you can then just use the native Year subtotal in your Employee hours module, if you have the summary options are set to "sum", so the formula would be Employee Hours.Hours[SUM:Employee Details.Shop]

    The challenge with the monthly "shop" attribute is what should you use for the year total?   Even if we know that the value doesn't change, the Anaplan engine doesn't and what if it did? How do we decide what shop is relevant for the employee when it changes per month. Hence why the structure is prevented.

     

    Using a SUM function for time over time is not possible do to the underlying way that the engine deals with time, but native rollups for month, quarter, year etc will automatically aggregate

    So the year totals in a module by month can be referenced in a module by year because the year value exists in the source directly.  If you try and map something by month AND try and aggregate, that is when you need the intermedate step(s)

    I hope this helps

    David

  • Thats great, thanks David