1 Reply Latest reply: Nov 6, 2012 6:02 AM by Jonathan Dienst RSS

    Multiple Date fields

      Hi All

       

      I have a table containing entries for all the documents in my system. Each entry features the document name, some metadata, and three (optional) date fields - Date Added, Date Reviewed, Date Signed off. I am wondering how I should implement this in QlikView - I have some experience with using a master calendar but with three date fields, eep.

       

      The goal is a graph, DATE as a Dimension and a line/area graph showing the total number of documents in the system, and how many have been reviewed over time etc.

       

      I am able to rejig the table in SQL if nessesary - would it be wise to rearrange the data so it has one date field? This would mean each document would appear three times, which confuses things as my main unique ID at the minute is DocID

       

      Just after some thoughts at this stage

       

      thanks

        • Re: Multiple Date fields
          Jonathan Dienst

          Hi

           

          I think that the best approach to your problem is to use a date island as the dimension. This is a date table that is not associated with the document data. I use a prefix is. on the field name of the island fields (is.Date, is.Month etc). Then you would use statements along the line of:

           

               Documents Added expression:

               =Count(Distinct If(DateAdded = is.Date, DocID))

           

               Documents Reviewed expression:

               =Count(Distinct If(DateReviewed = is.Date, DocID))

           

               etc

           

          i know Sum(If()) type expressions do not always perform well, but this should not be a problem unless your DocID table contains many millions of rows. And you cannot use set expressions here.

           

          Hope that helps

          Jonathan