configuring raw data from date/time fields

[#1]

In working on a support dashboard, I have two data sources for the same QlikView file. One sheet pulls data from an extensive MySQL database, while the second sheet gets data from a simple Excel spreadsheet. Here's the issue I'm having. In the Excel spreadsheet the date/time fields look like:

What I need to do is figure out a way to manipulate the date/time object so that I can create a way for users to select one of 12 months [Jan-Dec] or even drill down to a specific "Week". So far I can only convert the raw date/time data to MMM in QlikView and it gives me;

Which is not exactly what I need. I've only been doing this for two months and have not got a lot of this down pat, so this might be a simple issue for everyone out there, but here it's one of those frustrating things I've not found out how to do yet. I had thought about trying to manipulate the data in the original Excel spreadsheet, but would prefer to do it in QlikView as the source Excel file is a datadump from a third party that tracks customer surveys.

Any help would be GREATLY appreciated.

[GabrielAraya]

Hi, At firts place the Date filed is wrong .. or Qlikview don't understand it .. Why? Internally QV store the date information as a Julian value, so, If you display the Date information should be adjust to rigth hand .. ( numeric definition ). I recommend to use the Date# or TimeStamp# to say to QV what kind of data is your Date field.

BR

Gabriel

[#3]

"the Date filed is wrong .. or Qlikview don't understand it ..."

So what you're saying is that QlikView cannot interpret standard date/time fields? And that I will probably have to do the conversions I need outside QlikView in order to create a Jan-Feb-Mar.......................Dec group or arrange the dates into weeks?

I thought that you could import any data, and once in QlikView manipulate it as needed for your reports? Seems like a simple grouping of dates into Months and Weeks would be basic.

So do I need to do all the work outside QlikView or can it do it for me? And if so, how.....or what formula/code would I use to convert the date/time fields into a 12 month grouping, then into a 52 week group? Or can't this be done?

[#4 · Accepted solution]

Why not use the Month(), Week(), Day(), Weekday(), ... instructions?

When you load the close date add these:

LOAD ...,

Close_Date,

Month(Close_Date) AS Close_Month,

Week(Close_Date) AS Close_Week,

Day(Close_Date) AS Close_Day,

...

This way you can use the three new fields to select date parts.

Hope that answered your question.

[GabrielAraya]

OK, Let's start from the beginning:

Sometimes to see if QV is reading correctly a type of data, as a field that contains dates is very convenient to create a list object with the field, if the values are adjusted to left hand means that QV doesn't understood the data, all data should be aligned to right hand, to fix this issue, QV has some functions, like Date#, TimeStamp#, Time#.

In my script I could write: And the new field will have all the values adjusted to right hand. After that I can include in my script:
Year(TimeStamp#(Date,'MM/DD/YYYY hh:mm:ss TT')) as Ano,

Month(TimeStamp#(Date,'MM/DD/YYYY hh:mm:ss TT')) as Month,

Day(TimeStamp#(Date,'MM/DD/YYYY hh:mm:ss TT')) as Day;

That is all ..

Gabriel

[#6]

Indeed, it was easy as that. Using the Month(),Week(),Day() after the date time field in QlikView worked perfectly. Lesson learned and much appreciation. At some point I may get this project done. Awesome. Thanks very very much to all who responded.