Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
sajalgour2309
Contributor II
Contributor II

Convert YY/MM/YYYY to YY/MM

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

12 Replies
sunny_talwar

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')

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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

its_anandrjs
Champion III
Champion III

Try

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

Or

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

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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.



its_anandrjs
Champion III
Champion III

Check this with

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

Img2.PNG

sajalgour2309
Contributor II
Contributor II
Author

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 ?

sunny_talwar

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

sajalgour2309
Contributor II
Contributor II
Author

Sorry i meant 16/08/2017

sunny_talwar

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')