Is RangeSum the answer?

Fri, 10 Jan 2014 01:48:01 GMT

I have a problem in the insurance space. We need to count the number of Claims each month that have a Payment registered within that month or the two previous months. The user may filter the results by Payment Type and it should only count Claims there the Payment has been of that type.

Pseudo SQL for the calculation is:

Count(distinct ClaimId) where exists (Payment where TransactionDate between [Month End - 3 months + 1 day] and [Month End] and PaymentType in (??))

Simplified sample data:

ClaimId	PaymentId	PaymentAmount	TransactionDate	PaymentType
1	1	100	1 Jan 2013	A
1	2	150	1 Feb 2013	A
1	3	1000	1 Mar 2013	B
1	4	100	1 Mar 2013	A
1	5	100	1 Apr 2013	B
2	5	100	1 Feb 2013	A
2	6	100	1 Mar 2013	A
3	7	100	1 Dec 2012	A

If the user makes no selections in PaymentType, then the results should be:

Month	Count
Jan 2013	2
Feb 2013	3
Mar 2013	2
April 2013	2
May 2013	2
Jun 2013	1

If the user selects PaymentType ‘B’, then the results should be:

Month	Count
Jan 2013	0
Feb 2013	0
Mar 2013	1
April 2013	1
May 2013	1
Jun 2013	1

I do not think that RangeSum will work as it will calculate the first two months in the chart correctly.

topic Is RangeSum the answer? in QlikView

Is RangeSum the answer?