Filtering dashboards with aggregate measures

Q&A
, ,
1

Hello community,
I have a dataset with transaction data and I’m facing issues with filtering my views with aggregate measures.

Here is a sample of my data:

|day|source|client|status|amountRS|
|01/01/2024|A|1|processed|1|
|01/01/2024|A|2|processed|2|
|01/02/2024|A|1|processed|5|
|01/02/2024|B|1|processed|20|
|01/02/2024|B|1|canceled|100|
|01/02/2024|A|1|processed|50|
|01/02/2024|A|2|canceled|1|
|01/02/2024|A|1|processed|1|
|01/02/2024|B|2|canceled|1|
|01/02/2024|A|2|processed|1|
[…]

The business ran a marketing campaing to promote transactions processed through source A.
Therefore I need a view of historical volume (sum of AmountRS) of the clients that had its first transaction in source A after the campaign.

My attempt:

  1. Building an aggregate measure to identify the clients through a calculated field named “firstTransaction”.

minOver(
minIf(day,
source= “A” AND
status = “processed” ),
[{client}])

  1. Using this calculated field to filter my view (a vertical bar chart filtered with my calculated field)

In this process I got an error: “Table calculation attribute reference(s) are missing in field wells” (VISUAL_CALC_REFERENCE_MISSING)

Is there some way to get this view?
Thanks in advance.

2

Hello @flavia.aguiar , welcome to the Quicksight community!

That error means that you need to add fields from your calculated field to the field wells of your visual.

My bet is that you need to add more to add a different field to your X-axis or your Group By/Color by field well.

3

Hello, Duncan!
That’s the point… I would like to have a view of total volume filtered by my calculated field “firstTransaction” (a measure aggregated by “client” field).
If I add “client” to the field wells I would not be able to see total volume.

Regards,
Flávia

4

Hello Community!

Any other guesses?

5

Hi @flavia.aguiar, besides using custom SQL – I’m out of ideas. Check out some of the functions. Also, try using the Amazon Q Assistant on the function documentation page.
Amazon Q