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.
"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?
Why not use the Month(), Week(), Day(), Weekday(), ... instructions?
When you load the close date add these:
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.
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 ..