Convert YY/MM/YYYY to YY/MM

[sajalgour2309]

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

[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]

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]

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

[sajalgour2309]

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]

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