topic Re: Applying a Finincial Cap From an Input Value in App Development

Applying a Finincial Cap From an Input Value

Spartan27215 — Tue, 14 Dec 2021 22:56:58 GMT

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.

Re: Applying a Finincial Cap From an Input Value

RsQK — Thu, 16 Dec 2021 09:15:09 GMT

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:

Re: Applying a Finincial Cap From an Input Value

Spartan27215 — Fri, 17 Dec 2021 12:37:54 GMT

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.

Re: Applying a Finincial Cap From an Input Value

RsQK — Mon, 20 Dec 2021 06:24:35 GMT

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: