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!

Accepted answers

DavidSmith

@Tom_H

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

All comments

DavidSmith

@Tom_H

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

DmitrySorokin

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].

Tom_H

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?

DavidSmith

@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

Tom_H

Thats great, thanks David

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…