Use datafield based on sumOver in a filter

Question & Answer
1

Hello everyone,

I’ve been working on a dataset that contains information about multiple user accounts, and I need some help with filtering based on a calculated field. Let me explain the situation.

I have discovered that a person can have multiple accounts, and now I want to calculate the total amount across all these accounts. To achieve this, I have defined a field at the dataset level called “reserves per person.” Here’s the formula I’m using:

reserves per person := sumOver(sum(coalesce({account reserve}, 0) + coalesce({account non-vested reserve}, 0)), [{person}, {reference date}])

When I add this field to a table, it displays the exact results I was expecting. So far, so good.

However, my next goal is to use this calculated field as a filter in my table. Specifically, I want to include only those persons in the table who have a total of more than $100 across all their accounts.

I would greatly appreciate it if someone could guide me on how to use this calculated field as a filter. Any help or suggestions would be highly valuable.

Thank you in advance!

2

did you try to add a filter control by using the new calculated field? you may follow this document to setup Adding filter controls to analysis sheets - Amazon QuickSight

1 Like
3

The problem is if I can use a calculated field based on sumOver as a filter.

4

yes, you can use a created field with sumOver as filter.

in this example, I have a calculated field like below.
image

And using it as a filter
image

1 Like