How to use SumOver and POST_AGG_FILTER in a summary table?

Question and answer
#1

I have the following data:

reference date | person   | birth year | gender | account  | period from | period until | balance | minimum
2022-01-01     | JOHN DOE | 1990       | MALE   | EMPLOYER |  2015-01-01 | 2016-01-01   | 100     | 105
2022-01-01     | JOHN DOE | 1990       | MALE   | EMPLOYEE |  2015-01-01 | 2016-01-01   | 200     | 199
2022-01-01     | JOHN DOE | 1990       | MALE   | EMPLOYER |  2020-01-01 | 2021-01-01   | 500     | 495
2022-01-01     | JOHN DOE | 1990       | MALE   | EMPLOYER |  2020-01-01 | 2021-01-01   | 200     | 202

John Doe has worked from 2015 till 2016. The employer have saved $100 for him, but that should have been $105. The employee has saved in that same period $200, but he only should have saved $199. In total, John Doe is entitled to $300 which is lower than the minimum of $304. So he has a gap of $4 for the period 2015 till 2016.

In addition, John Doe has worked from 2020 till 2021. The employer have saved $500 for him, but he only should have saved $495. The employee has saved in that same period $200, but that should have been $202. In total, John Doe is entitled to $700 which is higher than the minimum of $697. So there is no gap for the period 2020 till 2021.

I’ve created the following calculations in the data set:

(1) balance per policy - per reference date and per period:
sumOver({balance}, [{reference date}, {period from}, {person}], PRE_AGG)

(2) minimum per policy - per reference date and per period:
sumOver({minimum}, [{reference date}, {period from}, {person}], PRE_AGG)

(3) gap per policy - per reference date and per period:
ifelse({minimum per policy - per reference date and per period} > {balance per policy - per reference date and per period}, {minimum per policy - per reference date and per period} - {balance per policy - per reference date and per period}, 0)

I want to create the following table.

birth year | MALE | TOTAL
1970       | 4    | 4

But QuickSight shows instead:

birth year | MALE | TOTAL
1970       | 8   | 8

I’ve tried to use PRE_FILTER or POST_AGG_FILTER instead of PRE_AGG. I think that POST_AGG_FILTER should be used, but then I have to add {reference date}, {period from} and {person} to the table. I’m not interested in the detailed results, but only in a summary table.

Can you help me to achieve this?

Ivan

#2

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 .

Screenshot 2022-06-23 at 18.29.56
Screenshot 2022-06-23 at 18.29.561451×710 162 KB

Regards,
Koushik

1 Like
#3

Start from next week, you can directly use sum to do it. If you would like to enjoy the new feature earlier, please contact @emilyzhu . Thanks.

1 Like
#4

That sounds excellent. I don’t mind waiting an extra week.

Can you already give some details how the SUM-function will have to be used? Is this a new function, or will you add another level to the SUMOVER-function ?

#5

will be:

(1) balance per policy - per reference date and per period:
sum({balance}, [{reference date}, {period from}, {person}])

(2) minimum per policy - per reference date and per period:
sum({minimum}, [{reference date}, {period from}, {person}])

(3) gap per policy - per reference date and per period:
ifelse({minimum per policy - per reference date and per period} > {balance per policy - per reference date and per period}, {minimum per policy - per reference date and per period} - {balance per policy - per reference date and per period}, 0)

1 Like