Period Over Period Growth Score

Question & Answer
, , , , ,
2

Hi @joeFRUS, the error you are encountering, VISUAL_CALC_REFERENCE_MISSING, typically arises when a table calculation is being referenced in a context where it cannot be computed properly. Table calculations in QuickSight, like periodOverPeriodPercentDifference, are computed based on the visible data in the pivot table, which means they can’t be referenced in the same way as a standard calculated field.

Here’s how you can resolve this issue:

  1. Precompute the Period Over Period Values: Instead of using table calculations directly in the pivot table, you can create calculated fields for the QOQ and YOY changes beforehand. This way, they are precomputed and can be referenced easily.
  2. Separate Calculated Fields: Create separate calculated fields for each component of your growth score. For instance, create one for QOQ change in pickups, YOY change in pickups, QOQ change weight, and YOY change weight.
  3. Aggregate Calculations: Ensure that these precomputed fields are aggregated correctly by Site. You might need to use aggregation functions like sum, avg, etc., depending on your requirements.
  4. Combine the Calculated Fields: Finally, create a calculated field for the growth score using the precomputed values.

Here’s a step-by-step guide to implement this:

Step 1: Create Calculated Fields for Period Over Period Changes

Create the following calculated fields:

  • QOQ Change in Pickups:
sum({Pickups}) - periodOverPeriodPercentDifference(sum({Pickups}), Date, QUARTER, 1)
  • YOY Change in Pickups:
sum({Pickups}) - periodOverPeriodPercentDifference(sum({Pickups}), Date, YEAR, 1)
  • QOQ Change in Weight:
sum({Weight (lbs)}) - periodOverPeriodPercentDifference(sum({Weight (lbs)}), Date, QUARTER, 1)
  • YOY Change in Weight:
sum({Weight (lbs)}) - periodOverPeriodPercentDifference(sum({Weight (lbs)}), Date, YEAR, 1)

Step 2: Aggregate Calculations

Make sure these fields are aggregated properly. In most cases, QuickSight will handle the aggregation automatically when you add these fields to your analysis. However, you might need to use aggregation functions if needed explicitly.

Step 3: Create the Growth Score Calculated Field

Now, combine these fields into your growth score formula:

(
  ((0.5 * {QOQ Change in Pickups}) + (0.5 * {YOY Change in Pickups})) +
  ((0.5 * {QOQ Change in Weight}) + (0.5 * {YOY Change in Weight}))
) / 2

Step 4: Add to Pivot Table

  1. Add Site as a row dimension in your pivot table.
  2. Add the newly created Growth Score as a measure in your pivot table.

Example

Your pivot table should now be able to display the growth scores aggregated by Site without encountering the VISUAL_CALC_REFERENCE_MISSING error.

By precomputing the period-over-period values and aggregating them correctly, you should be able to avoid issues related to table calculations not being computed in the desired context.

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and post it here. (Details on using Arena can be found here - QuickSight Arena