Get a count of open claims based on an independent date dimension

[villegasi03]

Qlikview users,

I am completely stumped on this problem that I am working on. I have a table with a claim number column, open date and close date column. Let’s say that for this example I have a month’s worth of data starting Jan 1st. I want to show in a graph the number of claims that are open on a particular date. Creating a pivot table with a count function as the expression and the open date as the dimension odiously will give me claims open one day one, let’s say 200 claims. If the next day Jan 2 we opened 100 claims for an accumulated total of 300 claims, out of those claims 50 were closed how can I show that a total of 250 are open on Jan 3.

I have a feeling this may be easier than I am thinking but I completely lost and dont even know if this is possible in qlikview.

Thank you,

[rwunderlich · Accepted solution]

A simple approach to this problem is to use IntervalMatch, matching the open/close range into a table of dates. For an example, see the QV Cookbook example "Count days in a transaction using IntervalMatch".

You can download the Cookbook from http://robwunderlich.com/downloads/.

-Rob

http://robwunderlich.com

[villegasi03]

Rob,

This worked perfect! Thank you for your assistance.