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: 
Not applicable

Date formatting problem for creating a Master Calendar

Hi,

I have a basic problem, I would like to create a Master Calendar. But I have problems in how to convert my "Date" in a adequate format.

I have a timestamp as Date in the format 'YYYY-MM-DD hh:mm:ss' and I would use this field to make my Master Calendar, for that I think I must convert it in a new field with the format 'DD.MM.YYYY' ==> I don`t now how to convert a timestamp in the dateformat which I mentioned above. For Example my basic timestamp of an excel file is ==> 2008-09-22 10:24:54 ==> must convert it in the format ==> 22.09.2008

And with this field with the right format than I can make a calendar which is described for example in the QlikView Cookbook.

Basic Setup of the Application

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

I hope somebody could help me? Thanks

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Herbert,

Dates are numeric in Excel, so the next sholud be working, given you set up your date format already:
date(floor(Date)) as Date

Regards,
Michael

View solution in original post

11 Replies
sparur
Specialist II
Specialist II

Hi. Herbert.

it's easy. 🙂

ex:

Transactions:

LOAD

field1,

field2,

date(fieldDate) as myDate

FROM ....

then make a calendar table:

MasterCalendar:

LOAD myDate,

year(myDate) as Year,

month(myDate) as Month

RESIDENT Transactions;

best regards, sparur.

Anonymous
Not applicable
Author

Herbert,

Dates are numeric in Excel, so the next sholud be working, given you set up your date format already:
date(floor(Date)) as Date

Regards,
Michael

sathishkumar_go
Partner - Specialist
Partner - Specialist

Hi

use Date(fieldname,'DD/MM/YYYY') AS Date

I think it ll help u..

sparur
Specialist II
Specialist II

in this case does not necessarily indicate the format. Big Smile

because the desired format specified in the basic constants of QV Document(SET DateFormat='DD.MM.YYYY';)

Not applicable
Author

Thanks for the answers, I have tried several types of date formatting before, but the date function in combination with the floor function brings me the right result, thanks to Solomovich and all the others for the help.

The problem with the other date functions was, that after generating a MasterCalendar for every Day also without Data ==> with the autogenerate function ==> there were no connection between the autogenerated data and my basic Timestamp Data, now it works fine.

Thanks to all

sathishkumar_go
Partner - Specialist
Partner - Specialist

Hi Sparur,

That 1 is qlikview default. Based on our data we need to change the formart. as per ur data u need to use date(fieldname(),'DD/MM.YYYY')

sparur
Specialist II
Specialist II

hi, Sathish.

not quite understand what you mean? I understand the function date () just takes the default value of the format from constant document: SET DateFormat...

I am wrong?

Not applicable
Author

I am very new to qlikview. I think with the date () function only we get not rid of the hh:mm:ss but the floor() function do this and the date() function than takes the default setting like DD.MM.YYYY, but I can be wrong. But with only the date function there was also the hh:mm:ss and thats a big problem when I want to make a calendar.

Thanks for all your help

sparur
Specialist II
Specialist II

That's right, because floor() rounding of date, so we get only date(without time)