6 Replies Latest reply: Feb 1, 2013 10:31 AM by Henric Cronström RSS

    How to create a chart over different date fields

      Hello,

       

      I have a table of records, each having a "Created on" and a "Closed on" date field. I also have a second table that maps a date to the corresponding work week.

      Currently I have created a chart with the work weeks as dimension and the number of records created in that work week as expression, by joining the two tables on the Created on date and using

      Sum(RecordCounter)
      

      (which is just the number 1 for each record) as the expression definition.

       

      Now my goal is to add a second expression for the total number of records closed on the respective work week. If I do another join with the master calendar on the Closed on date, I would have two different work week fields, but I need to have just one. Can you please point me in the right direction on how that can be achieved?

       

      Many thanks in advance.

        • Re: How to create a chart over different date fields
          Henric Cronström

          I would use three tables:

           

          1. Table1: Your original table with the two dates CreatedOn and ClosedOn. Let's call the primary key WorkITemID.
          2. Table2: The dates a WorkItemID is active. (Several dates per work item)
          3. Table3: A master calendar linked to Table2

           

          Table2 can be created through

          Load WorkItemID, Date(CreatedOn+IterNo()-1) as Date

             resident Table1

             While IterNo() <= ClosedOn - CreatedOn + 1;

           

          Then you can count the number of days per work item and the number of work items per date/week/month using a simple Count(distinct ...)

           

          HIC

            • Re: How to create a chart over different date fields

              Hi Henric,

               

              Thanks a lot. This is very helpful when I want to see the number of open records per week (I am using Count(distinct WorkItemID) ).

               

              But my primary goal is to get the number of records created and closed per week. So for a particular record that was created in week w1 and closed in week w2, I only want it to appear in w1 for the "Created" expression and in w2 for the "Closed" expression.

               

              Basically I would need something like 'Count(distinct WorkItemID) - "Count(distinct(WorkItemID of previous week" ' for the number of created records and 'Count(distinct WorkItemID) - "Count(distinct(WorkItemID of next week"' for the number of closed records. I'm sure there is a way to define this, but this is my first week into Qlikview and I'm still a bit overwhelmed by all its functions and features so any hints are very welcome.

               

              Thank you.

                • Re: How to create a chart over different date fields
                  Henric Cronström

                  Use the above data model, but load Table2 the following way

                   

                  Load WorkItemID, Date,

                     if(Date=CreatedOn, 1, 0) as IsCreatedOn,

                     if(Date=ClosedOn, 1, 0) as IsClosedOn;

                  Load WorkItemID, ClosedOn, CreatedOn,

                     Date(CreatedOn+IterNo()-1) as Date

                     resident Table1

                     While IterNo() <= ClosedOn - CreatedOn + 1;

                   

                  The first Load statement loads records from the second - or rather, the second Load is piped into the first. This way you get a flag for which day the work item is created and you can use Sum(IsCreatedOn) to count the created work items and plot that against week no.

                   

                  Create your master calendar this way, with the fields you want:

                   

                  Load distinct Date,

                     Week(Date) as WeekNo,

                     Date(MonthStart(Date),'YYYY-MMM') as Month,

                     Month(Date) as Month,

                     Year(Date) as Year

                     resident Table2;

                   

                  HIC

                    • Re: How to create a chart over different date fields

                      Awesome, thanks Henric! Now it looks like expected.

                       

                      The only issue I found was that for records that are not yet closed, no entries in Table2 were created because the while loop does not run.

                      I believe I was able to solve this by adding the following code:

                       

                      Concatenate (Table2) LOAD WorkItemID,
                          CreatedOn AS Date,
                          1 AS IsCreatedOn,
                          0 AS IsClosedOn
                      RESIDENT Table1
                      WHERE ISNULL("ClosedOn");
                      

                       

                      Thanks and regards.

                        • Re: How to create a chart over different date fields
                          Henric Cronström

                          That will solve the problem with the flags, but you will not get the dates between CreatedOn and today...

                           

                          An alternative is to check for NULLs when creating Table2, e.g.:

                           

                          Load WorkItemID, Date,

                             if(Date=CreatedOn, 1, 0) as IsCreatedOn,

                             if(Date=ClosedOn, 1, 0) as IsClosedOn;

                          Load WorkItemID, ClosedOn, CreatedOn,

                             Date(LowerBound+IterNo()) as Date

                             While IterNo() <= UpperBound - LowerBound;

                          Load WorkItemID, CreatedOn, ClosedOn,

                             if(IsNull(CreatedOn), ClosedOn, CreatedOn)-1 as LowerBound,

                             if(IsNull(ClosedOn), Today(), ClosedOn) as UpperBound

                             resident Table1;

                           

                          Then you will get the dates between CreatedOn and Today() also.

                           

                          HIC

                  • Re: How to create a chart over different date fields
                    Khadar basha shaik

                    Hi,

                     

                        i will provide test scripting for that one,

                     

                    Test:

                    load ID,

                          Name,

                          Createdate AS lInkDate,

                          close date

                         'Creat' AS Flag

                    from x Table;

                     

                    concatenate(Test)

                     

                    load ID,

                           Name,

                           CreatDate,

                           Closedate AS linkDate,

                          'Close' AS Flag

                    from x Table;

                     

                    Calendar:

                    load linkDate,

                            Month,

                            Year,

                            Week

                    from Calemdar;

                     

                     

                    closed and create both will  be link to same calendar

                     

                    w1 it display w1 -clsed according to closed

                    w1 it display w1-created according to created.

                     

                    i think it will be use full.