Extracting a week number from data in a YYYYWW format

[former_member704205]

Hi All,

I was wondering if somebody can assist me?

I'm trying to extract the week number from an object with a date format of YYYYWW (202001 - 202052) So basically the last 2 digits but I don't want the leading zeros for weeks 1 - 9. Also I would like it to be in a text format so I can merge with the rest of the weeks (Creating a new variable)

Is this possible?

Many thanks,

Alex

[former_member30]

Hi and welcome to the SAP Community!

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers (if you haven't already), as it provides tips for preparing questions that draw responses from our members. For example, you can outline what steps you took to find answers (and why they weren't helpful) and share screenshots of what you've seen/done. The more details you provide, the more likely it is that members will be able to assist you.

Should you wish, you can revise your question by selecting Actions, then Edit (although once someone answers your question, you'll lose the ability to edit the question -- but if that happens, you can leave more details in a comment).

Finally, if you're hoping to connect with readers, please consider adding a picture to your profile. Here's how you do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS. By personalizing your profile with a photo of you, you encourage readers to respond.

Cheers,

Julia SAP Community Moderator

[ayman_salem]

Use this formula

WW: =FormatNumber(ToNumber(Right([Date]; 2)); "##")

..

hope that helps

[former_member704205]

Hi Ayman,

Many thanks for your quick response!

I did try the formula you suggested but unfortunately it didn't work. I think its because the original date dimension (202001) is already a number that's why it gave me the below error. Ideally I would like the new week variable to be string, not sure if that's the correct way to do it but its worked in the past when merging the week numbers. Thanks for your help. Alex

Sorry, copying & pasting the image of my error doesn't appear to work either! Its says its an invalid data type.

[ayman_salem]

If the [date] dimension is a number, convert it to a string:

DateS: ="" + [Date]

Then use [DateS] instead of [Date] in the formula

WW: =FormatNumber(ToNumber(Right([DateS]; 2)); "##")

...

and if you prefer WW to be a string then like before:

WW: ="" + FormatNumber(ToNumber(Right([DateS]; 2)); "##")

[former_member704205]

Ayman, this worked perfectly!! Thanks for you help!!!

All the best!

Alex

[ayman_salem]

happy to see it work and help

kindly accept the answer and close it