topic Re: Working with dates in QlikView

Working with dates

Anonymous — Thu, 18 Dec 2014 19:40:31 GMT

Hi I have this table with date field in format M/D/YYYY and I want to show it like MMM-YY in QlikView, I want to create it from the script so I'm simply doing this date(SUBMIT_DT,'MMM-YY') which gets me the right format, the problem is, it doesn't group together in charts / list box... look at the picture, how can I solve it without converting it to text (so I can sort it correctly)

Re: Working with dates

maxgro — Thu, 18 Dec 2014 19:49:30 GMT

see attachment

Re: Working with dates

Anonymous — Thu, 18 Dec 2014 19:50:13 GMT

Hi oscar,

What do you mean by grouping?

is data duplicated?

Regards

Neetha

Re: Working with dates

MK_QSL — Thu, 18 Dec 2014 20:17:49 GMT

Add below in your script

Date(MonthStart(Date#(SUBMIT_DT,'M/D/YYYY')),'MMM-YY') as Submitted

use below in your chart as a Calculated Dimension or you simply use to create a field in list box

Date(MonthStart(Date#(SUBMIT_DT,'M/D/YYYY')),'MMM-YY')

Re: Working with dates

Anonymous — Thu, 18 Dec 2014 20:19:02 GMT

SIngle values in chart axis and list box, normally you can have multiple records with same value, but here they will appear just once

Re: Working with dates

jpapador — Thu, 18 Dec 2014 20:23:01 GMT

Use this on the date in the script:

Date(MonthStart(SUBMIT_DT), 'MMM-YY') as MonthYear,

Re: Working with dates

Anonymous — Thu, 18 Dec 2014 20:46:19 GMT

Thanks, works like a charm

Re: Working with dates

Anonymous — Thu, 18 Dec 2014 20:46:46 GMT

THanks you! I'd wish I could mark two answers as correct, I appreciate your help!

Re: Working with dates

MarcoWedel — Thu, 18 Dec 2014 20:47:24 GMT

LOAD Date(MonthName(SUBMIT_DT),'MMM-YY') as Submitted

Resident yoursource;