topic Re: Date format in QlikView

Date format

Anonymous — Mon, 06 Nov 2017 15:22:51 GMT

Hi All

I have dimension as below

=Month(CalDate) & ' ' & chr(39) & Right(Year(CalDate), 2) which gives me dimension as Jan '16, Feb '16 and so on (and this is fine)

I also have columns CallMonth as 1,2,3...12 and CallYear as 2016 & 2017 in my Data model

With this I' m unable to sort date

I have also tried to sort with below two

1. CallYear & CallMonth

2. Num(CallYear & CallMonth) both

Still the issue to sort

Thanks in advance

Re: Date format

sunny_talwar — Mon, 06 Nov 2017 15:24:15 GMT

May be try this as your dimension

Dual(Month(CalDate) & ' ' & chr(39) & Right(Year(CalDate), 2), MonthName(CalDate))

Re: Date format

antoniotiman — Mon, 06 Nov 2017 15:32:37 GMT

I suggest You to calculate Dimension like this

Date(CalDate,'MMM ''YY')

This is Dual Field

Re: Date format

sunny_talwar — Mon, 06 Nov 2017 15:36:35 GMT

I would suggest adding MonthStart here

Date(MonthStart(CalDate), 'MMM ''YY')

Re: Date format

Colin-Albert — Mon, 06 Nov 2017 15:48:08 GMT

or you could use monthname()

MonthStart will ensure that the numeric part of the date value is the 1st of the month and you are not getting a repeated value for each date. Monthname will show the date as MMM YYYY and will sort correctly

MonthName(MonthStart(CalDate))

Re: Date format

Anonymous — Mon, 06 Nov 2017 15:49:16 GMT

Hi Antonio

With this it hide some columns, I believe data model issue.

I appreciate your time and efforts.

Thanks

Re: Date format

sunny_talwar — Mon, 06 Nov 2017 15:49:43 GMT

I think if formatting is not needed, you can just do

MonthName(CalDate)

MonthName will essentially do the same thing as MonthStart...

Re: Date format

Anonymous — Mon, 06 Nov 2017 15:50:15 GMT

Hi Sunny

It works, thanks for suggestion@

Re: Date format

sunny_talwar — Mon, 06 Nov 2017 15:50:55 GMT

I am glad it helped