I would like to subtract one calculated field from another

Q&A
, , , ,
1

I have 2 fields calculated this way: Field 1 : runningSum(sum(MLV), [date ASC])
and Field 2: ifelse(
Ano = 2023 AND Seller = ‘teste1’, 100,
Ano = 2023 AND Seller = ‘teste2’, 200,
Ano = 2023 AND Seller = ‘teste3’, 300,
Ano = 2023 AND Seller = ‘teste4’, 400,
Ano = 2023 AND Seller = ‘teste5’, 500,
Ano = 2023 AND Seller = ‘teste6’,600,
Ano = 2023 AND Seller = ‘teste7’, 700,
Ano = 2023 AND Seller = ‘teste8’,800
/* Valor padrão se nenhuma condição for atendida */
0
)
I would like to perform a subtraction between them

2

Hi @Joanne

so you want to do {field 1} - {field 2} and you cant due to “aggregation missmatch” or what is your question?

BR

3

I want to do field1 - field2 but I can’t, is there any way to get the result I want?

4

why cant you? is there an error?

5

This error: Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.

6

you could try {field 1} - sum({field 2})

7

It worked, I have to check the values ​​now
In context, I have a table called “history” in dynamodb and it is updated every month. I have to create visualizations with your data.
Recently, we were asked to create visualizations relating to Seller goal values, and these goal values ​​are not in the history. I even created a table with these values ​​but I was unable to join the two datasets using direct query in quicksight.
There are other alternatives for using seller values

8

Doing it this way if I have, for example, Seller test 1 will add the value 100 every time test 1 appears, I would like the value 100 to be considered only once

9

You mean Avg instead of Sum?