How to Convert Number into Weekly format?

Hey Anaplanners,

Is there any way that a particular number or text can be converted into Weekly format? For example P1 2018 needs to be converted into Week 1 2018 in a Time Period Formatted Line item.

I know for the fact that we can create Date from number but there is nothing as such for Time periods esp weeks. Let me know if there is a way to do this.

Thanks,

Miz

Accepted answers

rob_marshall

@Misbah ,

So there are a couple of ways, but the best way of doing this is to create a list (Fake Time, in my case), where I have all periods defined (P1 2018 -> P20 2018). This will be used as a mapping list as well as in a mapping module.

Create the mapping module: SYS Mapping Time Module using the Fake Time in the Applies To

You will need to create the formulas in this order: Item, Period, Year, Text Member, Weekly Time Period

Line Items:

Weekly Rime Period: FINDITEM(Time, Text member)
Text Member:"Week " & Period & " FY" & year
***Notice the space after the "k" in week as well as the the space before the F in " FY"
Item:NAME(ITEM(Fake Time))
Period: RIGHT(LEFT(Item, FIND(" ", Item) - 1), FIND(" ", Item) - 1 - 1)
Year: RIGHT(Item, 2)

So now, you can link your text member by doing a finditem(Fake time, your txt) and get the weekly value by using a lookup ( Weekly Time Period[lookup:the finditem() you just created].

Hope this helps,

Rob

All comments

LipChean_Soh

Hi,

A date or text can be converted to a week as shown below:

Screen Shot 2019-06-04 at 5.01.53 PM.png Screen Shot 2019-06-04 at 5.02.01 PM.png

Regarding your specific example of converting P1 2018 to Week 1 2018, is P1 2018 a list member or a time from a data file? Perhaps the example above can give you some ideas?

Thanks,

LipChean

Misbah

Well you have taken the base of transformation as a date in text format whereas in my case it isn't date. It is just text formatted P1 2018 in data file.

rob_marshall

@Misbah ,

Create the mapping module: SYS Mapping Time Module using the Fake Time in the Applies To

You will need to create the formulas in this order: Item, Period, Year, Text Member, Weekly Time Period

Line Items:

Weekly Rime Period: FINDITEM(Time, Text member)
Text Member:"Week " & Period & " FY" & year
***Notice the space after the "k" in week as well as the the space before the F in " FY"
Item:NAME(ITEM(Fake Time))
Period: RIGHT(LEFT(Item, FIND(" ", Item) - 1), FIND(" ", Item) - 1 - 1)
Year: RIGHT(Item, 2)

So now, you can link your text member by doing a finditem(Fake time, your txt) and get the weekly value by using a lookup ( Weekly Time Period[lookup:the finditem() you just created].

Hope this helps,

Rob

Misbah

Thanks a lot! I think I will not use Fake list, instead I will do the data transformation in the source file and aggregate the date with transformed weeks.

rob_marshall

@Misbah ,

It would be best if you did do this in the source file, as you stated. Remember, Anaplan can also aggregate the data for you, meaning if your data is at the daily level, but the module you are importing to is at the weekly or monthly level, Anaplan will aggregate on the data load.

Thanks,

Rob

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…