How to create a chart over different date fields

[#1]

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.

[hic]

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

[#3]

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.

[hic · Accepted solution]

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

[Anonymous]

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.

[#6]

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.

[hic]

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