Find week (custom time dimension) from date in import

Highlighted
Certified Master Anaplanner

Find week (custom time dimension) from date in import

Hi all,

 

I'm working on a model which has a month format in my time dimension, this is nessecary because of workspace restrictions. But there is a need to add FTE using a begin and end week, so I've constructed a custom time dimension which looks like this: 

L_weeks.PNG

The weeks are genererated and are given a begin and end date in the same proces.

In my import file I have transactional data, so I have a date on which a certain amount of hours are written. Looking like this:

actuals.PNG

 

I would like to connect the date to a week using my beginning and end-date connected to my weeks. So the first record has the date 15-02-2017, which I would like to connect to week 7 2017.

 

L_weeks 2.PNG

I tried several things, but I just can't seem to get the correct formula(s). I hope someone can help my along.

Thanks in advance for your help. 

 

Cheers,

 

René den Uijl

 

3 REPLIES 3
Highlighted
Master Anaplanner/Community Boss

Re: Find week (custom time dimension) from date in import

Hi Rene

The simplest option is to create a mapping module (by day) that maps day to your timescale, then you can use this module in your transactional module to map to a line item formatted as your timescale list using a lookup

In the examples below, the timescale line items are formatted to my "fake timescale" list

Mapping module

2017-09-12_11-33-33.png

Transactional module

2017-09-12_11-33-59.png

Hope this helps

David

Highlighted
Certified Master Anaplanner

Re: Find week (custom time dimension) from date in import

Hi David,

This solution works but I would suggest to upgrade it - automate the formula in the new module's line item instead of values (Day Mapping.Timescale):

1) Create new line item inside weeks' list, let's name it as "Week Item" and put the formula: =Item(weeks' list)

2) And finally put the formula inside days' modules' line item (inside Day Mapping.Timescale): =IF ISBLANK(Weeks' List.Week Item[FIRSTNONBLANK: Weeks' List.Start]) THEN PREVIOUS(Timescale) ELSE Weeks.Week Item[FIRSTNONBLANK: Weeks' List.Start]

 

So you get automated solution

 

Thanks

Highlighted
Certified Master Anaplanner

Re: Find week (custom time dimension) from date in import

Hi Ansar and David,

 

Thank you both for the solution, it's working perfectly.

I thought I was almost there, but I just couldn't figure out the last step.

 

Cheers, 

 

René