topic Is it possible to create a case load over time graph? in QlikView

Is it possible to create a case load over time graph?

Thu, 08 Apr 2010 12:32:51 GMT

How do you display a cumulative number over time based on Open and Close dates?

Scenario: A database exist with support cases (or say Orders). Each has a StartDate, and if finalized, a CloseDate.

Is it possible to create a graph showing case load (or orders) in progress over time?

I.e, for each day in the defined period, a bar or line graph should indicate the number of cases (or orders) that were in progress on that day.

This means in effect that one entry for each day must be calculated by increasing with the number of StartDates that day, and be reduced by one for each Close Date. This number must then be added to that of the next day, which will either stay the same (if no cases were created or closed), increased (if new cases were opened) and reduced if cases were closed.

I Hope someone can help!

Is it possible to create a case load over time graph?

Oleg_Troyansky — Thu, 08 Apr 2010 14:01:04 GMT

The best approach would be to create a Calendar and link it to your "Orders" using INTERVALMATCH...

If your data set is small, you can probably get by with an island calendar and an IF() formula (checking IF the Calendar date is between the Start and the End Date).

Is it possible to create a case load over time graph?

Thu, 08 Apr 2010 15:18:53 GMT

Thanks

IntervalMatch seems like exactly what I want to do.

The help files are just a bit vague for me but I am working on it!

Is it possible to create a case load over time graph?

Thu, 08 Apr 2010 15:59:10 GMT

Ok, so I managed to create a TableBox, listing each date and each active case on that date.

I have an issue with some records not showing a date, but I'll look into that later.

How can I now create a graph of that table (assuming I sorted out the above problem)?

Is it possible to create a case load over time graph?

Thu, 08 Apr 2010 16:35:37 GMT

Records with no dates: The records showing no dates is for cases where it was closed on the SAME day it was created. Even though there is a time stamp in each date, the TableBox seems unable to process it for some reason...

Is it possible to create a case load over time graph?

Oleg_Troyansky — Fri, 09 Apr 2010 05:02:36 GMT

Paul,

it's not about the Table Box, it's more about the INTERVALMATCH linking... apparently those orders are not being linked to any dates. Check out your date fields - are those truly dates or maybe Timestamps (e.g. they have decimal portions)? You can try by forcing the StartDate to the bottom using function floor() and rounding the End Date to the top using function ceil(). To avoid counting one extra day, subtract a small amount like 0.01 from the End Date.

Is it possible to create a case load over time graph?

Fri, 09 Apr 2010 12:04:10 GMT

No, this database seems to be quite messy. It seems there is mixture(!) of dates in the datebase and trying the rounding functions on it make matters worst for most dates (not showing a date at all, that sort of thing).

In any case, this is a small problem compared to the issue of using the TableBox for any sort of calculation or graphing function!

Is it possible to create a case load over time graph?

Oleg_Troyansky — Fri, 09 Apr 2010 13:32:08 GMT

Table box is not the kind of object you want to use... It isn't recommended for any analytic purposes. We only use Table Boxes for a "quick and dirty" validation of the loaded data. You should be using Straight Table instead.

Is it possible to create a case load over time graph?

Fri, 09 Apr 2010 13:41:23 GMT

mmm... back to square one it seems!

Is it possible to create a case load over time graph?

Tue, 24 Aug 2010 07:49:43 GMT

You might find this usefull: http://community.qlik.com/forums/t/28595.aspx