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
-
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:
David
1
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].
1 -
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?
0 -
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
0 -
Thats great, thanks David0
