Date Field

[#1]

Hi,

I have used the list box to display only the Year of the Dates using the Code Year(Atime).

And the list box has the values as 2008,2009 accordingly. But In the Current selections box it is howing some Junk value as 7078 of 137351. It is the same case for the Month as well. Why Junk values are shown in the Current Selections Box.

Date is stored in milliseconds in database and i have converted in to Actual Date format using the To_Char by adding 1970. And it is working fine. Is it the reason the Current Selections showing Junk Values?

Regards,

VSJ

[pover]

The Junk values 7078 of 137351 tells me that QV sees the years as distinct value. Try playing with a couple QV functions:

year(date(Atime))

year(date(left(Atime,10)))

Upload a sample qvw if you can.

Regards, Karl

[#3]

Hi Karl,

I have tried ur suggestions stil the same Resposne. This is how the QVW file has been Created

Date in Database is milliseconds and i have converted to Datetime format in the Select statement itself. Display is fine no issues and Record selection everything is fine. Only in the Current Selections it is howing junk Value. I am not sure i will be able to share the QVW file.

Is there any options to solve this issue.

[pover]

JL,

Is there anyway you can upload a QVW file with just the datetime data?

Regards, Karl

[#5]

Hi karl,

I will not be able to share the file but i can share the Syntax how it has been done

Step:1 Date has been converted to dateformat in the select statement as follows

(TO_CHAR((TO_DATE('01/01/1970 00:00:00','MM/DD/YYYY HH24:MI:SS')+((Datefield)/(60*60*24))))

Step:2 Using the expression in Qlikview List box as below

Year( MakeDate(year(Datefield),Month(Datefield),Day(Datefield)))

It is how it has been done . In the list box it is shown as

2009 2010 2007 1970 2008

If i am choosing the date the asssoicate data is getting filtered correctly but the current selection box is Showing Junk Values.

Regards,

JL

[sicilianif]

It is not showing junk values. It is showing how many records were selected that contain the year portion that is selected.

You can get around this by using a calendar table. In this case the you would just need two fields; date and year. Then you would make a list box for the year.

If you need to know how to create the calendar table, just do a search in the forums. It has been discussed a couple times.

[#7]

It is not showing the how many records has been selected. The value is shown is the Junk values.

Calender table is again the Date and Time field. What is the difference it is going to make.

Irrespective of the calender table or not if it is date field and u have used the same it should show the Current selected value in the Current selection box it .

[sicilianif]

You are technically correct. It is not the number of records, but the number of unique date values. It is not however junk.

I have attached a sample with a very simple calendar so you can see the difference.

[#9]

But still i will say it is junk value since i have verified the count it is not as what said.

Thanks sicilianif,

I have saw your Calender Script.

Your Calender Script was like this

Date('1/1/2009'+Iterno()-1) AS Date,Year(Date('1/1/2009'+Iterno()-1)) AS Year

I have a question here what is the difference of using your script in the Edit Script section and at the Objects.

I am using the same Script at the Object level. but showing the Junk values in Selecion list.

[sicilianif]

Let's say there are the following dates in a field called ThisDate:

ThisDate
1/1/2010
1/2/2010
1/3/2010
1/3/2010

There are 4 records, but only 3 distinct values.

If you create a selection field with the expression Year(ThisDate), it will show 2010. When you select it, it will show 3 selections made, because it is couting the 3 distinct dates.

If you add a field called ThisYear in the current table, you would have this:

ThisDate ThisYear
1/1/2010 2010
1/2/2010 2010
1/3/2010 2010
1/3/2010 2010

There are still 4 records, but for the field ThisYear, there is only one disctict value.

If you now create a selection field for ThisYear, it will show 2010. When you select 2010, there is only 1 distinct value, and hence it will show just 2010.

Hopefully I have explained it well enough.