topic Date Field in QlikView

Date Field

Sat, 10 Jul 2010 14:23:07 GMT

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

Date Field

pover — Sat, 10 Jul 2010 20:46:52 GMT

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

Date Field

Sun, 11 Jul 2010 10:14:19 GMT

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.

Date Field

pover — Mon, 12 Jul 2010 17:13:35 GMT

JL,

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

Regards, Karl

Date Field

Mon, 12 Jul 2010 18:51:22 GMT

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,

Date Field

sicilianif — Mon, 12 Jul 2010 19:58:13 GMT

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.

Date Field

Wed, 14 Jul 2010 05:44:55 GMT

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 .

Date Field

sicilianif — Wed, 14 Jul 2010 13:43:57 GMT

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.

Date Field

Thu, 15 Jul 2010 06:26:07 GMT

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.

Date Field

sicilianif — Thu, 15 Jul 2010 12:56:49 GMT

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.

Date Field

Thu, 15 Jul 2010 13:16:42 GMT

Thanks sicilianif . It is very clear your explanation.

My query is while creating the calender table you have used the Year and Date function. I am using the same functions in the Object.

If it is created as calender table it is showing the year and Date in the Current selection box.

If it is created at object level it is showing the distinct count.

Is it the Bug in Qlikview or the Functionality of the Qlikview. Can u please explain me to understand that.....

Thanks in Advance for the same.