topic Re: Data not presented in correct timeperiod in QlikView

Data not presented in correct timeperiod

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Data not presented in correct timeperiod

swuehl — Tue, 11 Oct 2016 22:22:02 GMT

Maybe have a look at

Creating Reference Dates for Intervals

Re: Data not presented in correct timeperiod

swuehl — Tue, 11 Oct 2016 22:25:12 GMT

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

Canonical Date

Re: Data not presented in correct timeperiod

DavidFoster1 — Wed, 12 Oct 2016 10:36:35 GMT

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.