Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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

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

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