topic Re: Convert YY/MM/YYYY to YY/MM in QlikView

Convert YY/MM/YYYY to YY/MM

sajalgour2309 — Wed, 25 Nov 2020 16:16:04 GMT

Hello guys,

i have a date column in my data source and it's format is YY/MM/YYYY (17/08/2017). In Qlikview i want date in YY/MM format.

Field name - OrderDate

I have tried Date((Date#(OrderDate, 'YY/MM/YYYY')), 'YY/MM') but it doesn't work.

Am i doing something wrong here ?

Sajal

Re: Convert YY/MM/YYYY to YY/MM

sunny_talwar — Thu, 24 Aug 2017 15:47:59 GMT

This worked for me

Date(Date#('17/08/2017', 'YY/MM/YYYY'), 'YY/MM')

What issue are you having?

Alternatively, try this

Date(Date#(Left(OrderDate, 5), 'YY/MM'), 'YY/MM')

Re: Convert YY/MM/YYYY to YY/MM

Oleg_Troyansky — Thu, 24 Aug 2017 15:53:17 GMT

Perhaps your original date format is 'DD/MM/YYYY' ? That problem alone would make the formula incorrect...

If your date is already formatted as a proper QlikView date (the easier way to test is to change the number format to Integer and verify that you see 5-digit numbers, starting with 4) - then you can simply format it any way you want:

date(OrderDate, 'YY/MM')

Now, this is still going to be your original date, only presented as a Month. If you wanted to actually replace the date with the Month (so, all 30 days of that month should be replaced with a single value), then you should add the MonthStart() function:

date(MonthStart(OrderDate), 'YY/MM')

If your date was loaded as a string (you don't see integer values when you change the Number format), then you need to cast your string into the proper date value, like this:

Date#(OrderDate, 'DD/MM/YYYY')

After that, you can format the result any way you want, as suggested earlier.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

Re: Convert YY/MM/YYYY to YY/MM

its_anandrjs — Thu, 24 Aug 2017 15:56:12 GMT

Try

=date(Date(today(), 'YY/MM/YYYY'),'YY/MM')

=Date((Date(OrderDate, 'YY/MM/YYYY')), 'YY/MM')

Re: Convert YY/MM/YYYY to YY/MM

Peter_Cammaert — Thu, 24 Aug 2017 16:02:34 GMT

Funny, how the Date#() function tries to recover from an invalid format string. If specifying the year twice is to be considered invalid. What else would you do with something like Date#('16/08/2017', 'YY/MM/YYYY') ?

Fortunately, you can specify many years, but only the last one will be used. For example,

Date(Date#('16/08/2017', 'YY/MM/YYYY')) = Aug 1st, 2017.

Re: Convert YY/MM/YYYY to YY/MM

its_anandrjs — Thu, 24 Aug 2017 16:12:06 GMT

Check this with

=date(Date(today(), 'YY/MM/YYYY'),'YY/MM')

Re: Convert YY/MM/YYYY to YY/MM

sajalgour2309 — Thu, 24 Aug 2017 17:10:09 GMT

Thanks a lot for all the answers. I am sorry if my question was not clear.

So basically i have a Orderdate column which has values like '16/01/2017', '16/02/2017'......, '17/01/2017'

It should take '16/01/2017' as Aug-16 and not as Jan-17.

Date(Date#(Left(OrderDate, 5), 'YY/MM'), 'YY/MM') doesn't seem to work

Any other idea ?

Re: Convert YY/MM/YYYY to YY/MM

sunny_talwar — Thu, 24 Aug 2017 17:16:12 GMT

How can 16/01/2017 be Aug-16? Where is the Month information in 16/01/2017? Did you mean 16/08/2017?

Re: Convert YY/MM/YYYY to YY/MM

sajalgour2309 — Thu, 24 Aug 2017 17:29:29 GMT

Sorry i meant 16/08/2017

Re: Convert YY/MM/YYYY to YY/MM

sunny_talwar — Thu, 24 Aug 2017 17:33:29 GMT

If it is 16/08/2017, I don't see a reason for this not getting read

Date(Date#(Left(OrderDate, 5), 'YY/MM'), 'YY/MM')

Re: Convert YY/MM/YYYY to YY/MM

sajalgour2309 — Thu, 24 Aug 2017 17:37:54 GMT

I think Date(Date#(Left(OrderDate, 5), 'YY/MM'), 'YY/MM') is not working because OrderDate is not text like '16/08/2017' it's actual date like 42963.

As you suggested sunny I have tried Date(Date#(Left('16/08/2017', 5), 'YY/MM'), 'YY/MM') and it worked.

but it won't work when i will pass OrderDate.

Re: Convert YY/MM/YYYY to YY/MM

sunny_talwar — Thu, 24 Aug 2017 17:39:30 GMT

Then may be try this

Date(Date#(Left(Date(OrderDate, 'DD/MM/YYYY'), 5), 'YY/MM'), 'YY/MM')

Re: Convert YY/MM/YYYY to YY/MM

sajalgour2309 — Thu, 24 Aug 2017 17:46:57 GMT

Great. It works fine.

Thanks a lot Sunny and others.