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

[#1]

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!

[Oleg_Troyansky]

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).

[#3]

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!

[#4]

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)?

[#5]

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...

[Oleg_Troyansky]

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.

[#7]

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!

[Oleg_Troyansky]

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.

[#9]

mmm... back to square one it seems!

[#10]

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