Applying a Finincial Cap From an Input Value

[Spartan27215]

I need to be able to apply a CapValue based on a single persons MedClaims within the time period of the filter context.

In SQL I would do this by Summing each Persons Medclaims dollar amount. If the amount is greater than CapValue I would use the Cap Value as the dollar total, otherwise I would use the Sum amount. I am trying to figure out how to apply this concept to a Qlik Measure.

Example:

CapValue = 50,000

PersonId     Sum          Applied Value

1                     20,000          20,000

2                     75,000          50,000

3                     35,000          35,000

4                     50,001          50,000

5                    100,000         50,000

Total Sum to be returned by measure would be

20,000+50,000+35,000+50,000+50,000 = 205,000

I feel like I should be able to use the aggr function similar to aggr(ClaimValue, PersonId) to summarize the data by person, but no idea what to do with it after than. There do not seem to be any other functions that will deal with the output of this one to apply the comparison logic to etc.

[RsQK]

Hi, you could do this:

Create a variable named vCapValue with the value of 50000, then use the variable in the measure like this:

=SUM(AGGR(RANGEMIN(SUM(Sum),$(vCapValue)),PersonId))

The result:

[Spartan27215]

Not sure this will work when each of the persons has many claim amounts within the time period. in fact, when applying this to my situation reflects the number Zero, probably because at some point in the time period there was a zero claim amount.

In theory this would be the start

Aggr(MedPlanPaid, PersonId), this would return the sum of the claim amount by each person as an array. The issue at this point is how to compare the CapValue to each of these array entries to either use the CapValue or the claim value, and then sum these.

[RsQK]

What the formula does is it calculates the total amount of MedPlanPaid for each PersonId, then compares it to the vCapValue and gives the result, there's absolutely no difference whether there's 1 record per PersonId or a million. I generated a test table with this code:

data:
LOAD
ROUND(RAND(),0.25) AS MedPlanPaid,
MOD(ROWNO(),100)+1 AS PersonId
AUTOGENERATE 10000000;

Resulting data set has 100k records per PersonId, also zeros as the MedPlanPaid. The formula I suggested before still works: