Acquired Amount within overlapping dates

[JMAROUF]

Insurance is one of the most complicated sectors in data analytics,   developers  struggle when it's required to calculated the acquired amount within the year, and especially those which overlap,  here is an example to solve such kind of  issues with Load Script:

The picture below shows some subscribed  amount in different periods, but the customer needs the acquired amount within the year, if we take policy N°  004 as an example, we notice that we have a 7000,00 $ for a period of three years (2021 --> 2023), but how to proceed if the customer has to analyse year by year ? in my opinion it's usefull  to create as  many lines as we have in the covered period (EndYear-StartYear),  i have used some dummy data to demonstrate the solution discussed above:

then i try to load the whole row as one field in order to load variables separately:

it's time to call our generator, for each row we should create as many line as we have in the whole covered period:

finally here's the generator of the result table:

here is the output:

expressions used:

DiffDays:                       Round((EffectiveEndDate-EffectiveStartDate)+1)

DaysOfYear:                Round(YearEnd(EffectiveEndDate)-YearStart(EffectiveStartDate))

CoveredPeriod %:   Round((EffectiveEndDate-EffectiveStartDate)+1)/Round(YearEnd(EffectiveEndDate)-YearStart(EffectiveStartDate)) 

AcquiredAmount:   Sum((Amount*((EffectiveEndDate-EffectiveStartDate)+1))/((EndDate-StartDate)+1))

Note: this solution can impact performance when used with huge fact, i remain open for any suggestion or alternative.

Best ragards:

JM;

[Gabi3]

Gracias por sus soluciones mas interesado