When you don’t get the results that you expect, I found that the best way to troubleshoot is to create a table visual and add every single field to it. It will show you where your duplicates are coming from. You can see the duplicate values below.
For example, the sum of the premiums for Participant ID 101 should be 125,000 + 8,937,100 + 7,162,730. However, after you perform your join, each value appears twice in your dataset, so if you just do a sum, it will be twice bigger than the correct sum.
There are 3 ways you can approach this:
Approach #1:
If you know there are duplicates, count how times each value appears in your dataset and divide by that number.
Rows per Claim = countOver({Annual Premium}, [{Participants ID}, {Insurance Period}], PRE_AGG)
Divide the value that you want to sum by the number of times that it appears (2 in this case).
Weighted Claim Amount = {Claim Amount} / {Rows per Claim}
Then take the sum of Weighted Claim Amount to get the correct sum.
Approach #2
Find a way to flag the first instance of each Claim Number ID and take the sum of all claim amounts with the flag.
Row Number = Rank([{Annual Premium} ASC], [{Claim Number ID}], PRE_AGG)
First Row = ifelse({Row Number} = 1, 1, 0)
Claim Amount (Deduplicated) = {Claim Amount} * {First Row}
Finally take the sum of Claim Amount (Depuplicated).
Approach #3
Nest a LAC-A function in an aggregate function.
Total Claim = sum(min({Claim Amount}, [{Claim Number ID}]))
What this is saying is basically take the min of every claim amount in your partition (Claim Number ID) and then take the sum of all those minimum values.
