Hi,
The reason why you have 8 is because the aggregation is happening at the dataset level and the values are repeated for each record, but I understand your requirement is use the single value from that set for the final visualisation.
The following post has a solution implemented by @robdhondt : SumOver -Summing distinct entries from a table with duplicate rows . I have used the same logic.
Calculations:
rank_1record : rank([balance ASC],[{reference date}, {period from}, person],PRE_AGG)
measure_flag : ifelse({rank_1record}=1,1,0)
gap_measure : ifelse({measure_flag}=1,{gap per policy - per reference date and per period},NULL)
gap per policy : sumOver({gap_measure},[],PRE_AGG)
Note : Included an additional gap value ( year 2020-2021 ) , so the total is 6 .
Regards,
Koushik