Quicksight Calculated Field Error - Pace Linearity and Difference

Question & Answer
, ,
1

Hi - I have the following data in Quicksight:

  • MTD Revenue: $210
  • Revenue Goal: $480
  • % of Month: 42% (13 days so far / 31 days in the month, showing only the whole number)

I want to calculate three fields:

  • Pace Budget = Revenue Goal * % of Month
  • Pace Difference = MTD Revenue - Pace Budget
  • Pace Variance = Pace Difference / Pace Budget

I’m able to get the Pace Budget field to calculate correctly ($201, rounded to nearest whole number), but Pace Difference says $1 when it should be $9 ($210 - $201) and Pace Variance seems to be calculating off that $1 as it says variance is 1% (rounded to nearest whole number) instead of 4% ($9/$201).

In the Quicksight dashboard, I’m aggregating the numbers as “max” because in any other format the values are way off.

2

Hello @vrn2vw , welcome to the Quicksight community!

When you ran you calculations, did you run them exactly you have written them above?

For Example:
Pace Difference: {mtd revenue} - {pace budget}

If so, I would try adding an aggregate to the field prior to using the operator like the example below:

Pace difference: sum({mtd revenue}) - sum({pace budget})

Let me know if this helps!

1 Like
3

I am mimicking the same logic, but it’s not working

When I try adding “sum({mtd revenue}) - sum({pace budget})” then I get this error: “Your calculated field expression contains invalid syntax.” I’m not sure how to remove this error.

4

@vrn2vw Does your calculation look like the one in this screenshot?:
Screenshot 2023-08-15 135951

5

Hey @vrn2vw! Were you able to try the calculation syntax above?

6

Hi @vrn2vw

We have not heard back from you regarding your question. We would still like to help. If we do not hear back in the next 3 days, we will archive the question.

7

Hi Max - Sorry meant to respond. The answer was partially correct, I needed to use max instead of sum to get the calulation to work correctly: