Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
maternmi
Creator II
Creator II

to convert week-year to day-month-year format

Hello,

the purchase order date is shown in the most cases as DD.MM.YYYY, but sometimes also as calendar week-year e.g. 43.2013. How can I convert it to a single date format (DD.MM.YYYY), to show the last day of the week. In this case cw 43.2013 should be convert in

25.10.2013

Thank you for your help!

Best regards,

Michael

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

You can use the same MakeWeekDate expression in the UI, but surely this would be much better corrected in the script?

View solution in original post

9 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try makeweekdate()

Regards,

Jagan.

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

 

=Alt(DateDimensionName, WeekEnd(MakeWeekDate( Right(DateDimensionName, 4), Left(DateDimensionName, 2)))

Hope this helps you.

Regards,

Jagan.

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Try:

If(Len(PODate) <> 10

     ,MakeWeekDate(Right(PODate,4),Left(PODate,2),5)

     ,PODate)

                         AS     PODate

The third parameter given as "5" will return the Friday of the week. Adjust as desired.

Hope this helps,

Jason

Not applicable

use this derived field into ur script

=if( Len(OrderDate)<=7,Date(WeekEnd(MakeWeekDate(SubField(OrderDate,'.',2),SubField(OrderDate,'.',1)))-2),OrderDate) as Orderdate1

Regards,

Kabilan K.

maternmi
Creator II
Creator II
Author

I'm sorry, I haven't mention that I need a solution outside the script.

My dimension looks as follows

Delivery date (culomn name)

10.04.2012

11.07.2013

43.2013    --> Would like to have 25.10.2013

07.10.2013

16.2013   --> Would like to have 19.04.2013

Thanks & Best regards,

Michael

Jason_Michaelides
Luminary Alumni
Luminary Alumni

You can use the same MakeWeekDate expression in the UI, but surely this would be much better corrected in the script?

tresesco
MVP
MVP

May be like attached sample?

maternmi
Creator II
Creator II
Author

Hi Jason,

coul you please explain me the syntax? For better understanding.

Thanks!

Best regards,

Michael

Jason_Michaelides
Luminary Alumni
Luminary Alumni

From the F1 help:


MakeWeekDate(YYYY [ , WW [ , D ] ] )

Returns a date calculated from the year YYYY, the week WW and the day-of-week D.

If no day-of-week is stated, 0 (Monday) is assumed.

Examples:

makeweekdate(1999,6,6) returns 1999-02-14

makeweekdate(1999,6) returns 1999-02-08

In the solution provided, you first test the format of the current row value using Len().  If it is not 10 chrs long (DD.MM.YYYY = 10chrs) then use MakeWeekDate(),  Pass in the Year using Right(YourDate,4), pass in the Week using Left(YourDate,2) and define the day of the week you want to return the date for (omitted or 0=Monday).

Hope this helps,

Jason