4 Replies Latest reply: May 31, 2011 9:57 AM by khetienne RSS

    Cannot filter on Date



      It's been a while since I used QV, and I am quite rusty!


      I have an excel file that ( for this example) contains 2 fields, the date and a site derscription


      So, I load 'Date' and 'Site', as a 'tempMain', , reload as 'Main' to allow the ORDER BY and then use a date script I found on the web to generate dates from the earliest date in the 'tempMain' table





      load TempDate as Date,

      week(TempDate) as Week,

      Year(TempDate) as Year,



      Now, I create two tables, linked by the key field 'Date'




      When I select "Year" , ALl my "sites are deselected, but in theory, if I select 2011 in Year, the key should link back to all the dates in the Main that are in 2011


      ... shoudl it not..?..!


      Assistance please!

        • Cannot filter on Date
          Dennis Hoogenboom

          Most of the time this means that your 2 date fields are not the same format.

          Try to find it in this.


          If you can share your QV-document I will take a look.

          • Cannot filter on Date
            Erich Shiino

            you can press Ctrl+T to check the links between tables.

            On your main table you have TempDate or Date as a date field?

            I guess it's not properly linked.

            Maybe, you could use just :




            load TempDate,


            week(TempDate) as Week,


            Year(TempDate) as Year,



            Hope it helps,



            • Cannot filter on Date
              Ali Hijazi

              if your date column in the excel sheet contains values with time then if you use the num function to get the numeric representation of your date then it will give you a decimal value

              whereas if you use the num with the TempDate of the master calendar it will give you an integer

              to solve this problem use the following on the column of the excel sheet date(floor(columnName))


              the floor function takes the integer part of the date and the date() function gives you the date format that can be linked with the TempDate of the master calendar

              • Cannot filter on Date

                Thanks All. Every response was useful, in that it told me what to looko for and gave me the solution