Data not presented in correct timeperiod

[#1]

A question from a novice in QlikView.  I have simplified the example to make it easier for me to explain the problem. We have a list of cases and want to show in one graph how many that starts within a defined time period (year) and another graph that shows how many that is solved in a time period (year). The table could look like this:

Case#  Start      End

1            2015      2015

2            2015      2016

3            2015      2016

4            2016      2016

The load in the qvw file looks like this:

Fact:

LOAD Case#,

          Start as %Date,

          End

FROM

…

%Date is connected to a table where year, week, day, weekday etc are defined.

The expression: Count(Start) creates this result in the graph:

2015: 3

2016: 1

The expression: Count(End) creates an unwonted result in another graph:

2015: 3

2016: 1

I expected it to be:

2015: 1

2016: 3

I know QlikView count the End correctly, but it is presented in the period the case started. I think this has to do with using Start as %Date, but I don’t understand what the solution is that makes it possible to present the data correctly.

[swuehl]

Maybe have a look at

Creating Reference Dates for Intervals

[swuehl · Accepted solution]

Or if you are only interested in the Start and End dates and not in the dates inbetween:

Canonical Date

[DavidFoster1]

I would use a crosstable to create a CaseEvents table that has columns

CASE, EVENT, DATE

That way you will be easily able to show volumes of case start event and close events occurring on the same dates.