topic Date format in QlikView

Date format

Mon, 13 Feb 2012 13:13:59 GMT

Hi all!

I've tried to make a simple sales/year chart but I'm struggling with the dates...

I get my data from an odbc and the date format is YYYYMMDD (e.g 20120213). When I try to change the number format settings to dates the values are just nonsense...

What should I do in order to get the chart so that it would show sales/years then months and days?

Cheers!

Date format

jagan — Mon, 13 Feb 2012 13:18:55 GMT

Hi,

Use the following example of Load statement

LOAD

Date,

Year(Date) AS Year,

Month(Date) AS Month,

Day(Date) AS Day

LOAD

Date(Date#(DateField, 'YYYYMMDD')) AS Date

FROM ABC.txt;

In the above script you can arrive the Year, Month, Day and Date in valid format. By using Month, Year and Day dimensions you can create the charts.

Regards,

Jagan.

Date format

CELAMBARASAN — Mon, 13 Feb 2012 13:21:02 GMT

Hi,

Use expression like this

Num(Date#(YourDateField,'YYYYMMDD'))

Hope it helps

Celambarasan

Date format

Mon, 13 Feb 2012 13:55:39 GMT

Hey Jagan!

Thanks for the quick reply!

I did not quite get this part

LOAD
Date(Date#(DateField, 'YYYYMMDD')) AS Date

In my script I have

ODBC connect to...

SQL select......

From...

To which part should I put that? I got a few errors when I added to My date field's name is 'lstupddte'

Date(Date#(DateField, 'YYYYMMDD')) AS Date

Thanks and regards!

Antti

Date format

CELAMBARASAN — Mon, 13 Feb 2012 14:00:30 GMT

Hi,

You can use like,

Load

Date(Date#(DateField, 'YYYYMMDD')) AS Date;

SQL select......

From...

Instead of * you can use the field names from the SQL Select query.

Hope it helps

Celambarasan

Date format

jagan — Mon, 13 Feb 2012 14:05:07 GMT

Hi,

Date and Date# are qlikview functions. It should not be used in SQL queries. So first load the data from SQL and then using Preceding Load change the date format and arrive the required columns. The example of preceding load is

Load

Date(Date#(DateField, 'YYYYMMDD')) AS Date;

SQL select......

DateField,

From TableName;

Regards,

Jagan.

Date format

Mon, 13 Feb 2012 14:34:31 GMT

Hi,

Thanks a lot! Now I got the date format in order!

Date format

Tue, 14 Feb 2012 07:06:22 GMT

Hi,

One more question relating to this...

Now that I have the date format set up, how can I do the sales/year so that I can drill down to months and days?

Thanks a lot in advance!

Re: Date format

Anonymous — Wed, 15 Feb 2012 06:37:57 GMT

You can achieve your solution using the pivot table .

I attached one example qv file please refer.

Re: Date format

Anonymous — Wed, 15 Feb 2012 06:44:09 GMT

Also You can use the drill down option from chart to achieve your solution.

See the attached file it will helpful

Regards

Ashok

Re: Date format

Wed, 15 Feb 2012 07:31:33 GMT

Thanks Ashok for the help!

I got it working

Regards

Antti