Multiple Date fields

[#1]

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

[jonathandienst]

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