Period Over Period Growth Score

I am attempting to create a growth rate score, I have a formula that I believe is working:

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

The variables you see are all their own calculated fields, and they take a date value, pickup count, and pickup weight. An example of one of the variable fields:

periodOverPeriodPercentDifference(sum({Weight (lbs)}),Date, QUARTER, 1)

All of the variable fields are the same structurally, the only differences are the period (year over year or quarter over quarter) and the metric(weight or pickup count).

I have a pivot table to test the variable fields and all of the variable fields are returning the expected values.

I am trying to show these scores by ‘Site’ which is a dimension. When I put Site and Growth Score into a pivot table I get an error: VISUAL_CALC_REFERENCE_MISSING

After researching it looks like period over period is a table calc, which I’m worried means the way I have constructed this will just not work.

Any workarounds or suggestions for how I could get the result I’m looking for? I need that formula to output a result, and I need to be able to display that result aggregated by ‘Site’.

https://community.amazonquicksight.com#Arena-dashboardId=cb565fe1-e334-422b-a9b1-1349fd36dcea&authorId=CU-12257

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

Hey thank you for your reply @Xclipse! I understand conceptually the need for pre-aggregation so that aspect of your solution made sense to me, the calculated fields you described in step one I think will return total rescuer count (for example) minus the period over period change, which is not quite what I’m looking for, I really would like to return just the change rate for the score formula so that each element of the formula is proportional.

Is there an alternate way to achieve the precompute element?

Here is the Arena dashboard: (https://community.amazonquicksight.com#Arena-dashboardId=cb565fe1-e334-422b-a9b1-1349fd36dcea&authorId=CU-12257)

Hi @joeFRUS, maybe we can use PRE_AGG as described here. Or we can try Level-Aware Calculations. You would have to experiment and figure out which function works best. Here is a list of functions by category.

In the meantime, I will also be researching, let’s keep this forum open for a few more days.

Hi @joeFRUS,
I’m just checking in on this question, as we have not heard back from you. Did the responses from @Xclipse help? If we do not hear back in the next 3 days, we will archive the question.
Many Thanks,
Andrew

Hey @abacon @Xclipse, I wasn’t able to make either of those solutions work for me, but I have made some progress and believe I am pretty close.

I have four calculated fields, Yweight (yoy change in weight), Ypickups (yoy change in pickups), Qweight (qoq change in weight), and Qpickups (qoq change in pickups).

I have two more calculated fields:

Pickup Growth Score: (0.5Qpickups) + (0.5Ypickups)
Weight Growth Score: (0.5Yweight) + (0.5Qweight)

And then a final calculated field:

Site Growth Score: (Pickup Growth Score + Pickup Growth Score)/2

If I create a pivot table with Site and Date as dimensions, and Site Growth Score as the metric, I get the correct results and I’ve done a lot of QA and am getting the correct data at the end.

Issue:
I need to see the current score for each site, which for all intents and purposes will be the score at the end of the last full quarter. Right now there are multiple entries for each site because it’s including every date period on the visual. Any date filter I place on the visual either breaks a leg of my growth score or results in multiple entries for each site.
Thoughts?

Hi @joeFRUS, it sounds like you are almost there. To display only the score for the last full quarter for each site, you can use the following approach:

Steps to Display the Current Score for Each Site

  1. Create a Filter for the Last Full Quarter:
  • Create a calculated field to determine if a date is in the last full quarter. For example, if your Date field is called Date, the calculated field might look like this:
ifelse(quarter({Date}) = quarter(addDateTime(-1, 'QTR', truncDate('QTR', now()))), 1, 0)
  1. Apply the Filter:
  • Apply a filter to your analysis where this calculated field equals 1. This will filter your data to only include the dates from the last full quarter.
  1. Update the Pivot Table:
  • With the filter applied, your pivot table should now only show the data for the last full quarter.
  • Ensure that your pivot table includes Site as the dimension and Site Growth Score as the metric.

Example Calculated Field:

ifelse(quarter({Date}) = quarter(addDateTime(-1, 'QTR', truncDate('QTR', now()))), 1, 0)

Apply the Filter:

  1. Go to the analysis page.
  2. Click on the filter pane.
  3. Add a filter based on the calculated field created.
  4. Set the filter to include only values where the calculated field equals 1.

By filtering the data to only include the last full quarter, you can ensure that your pivot table displays only the current score for each site.

Hope this helps.

Hey @Xclipse I appreciate the response and I’ll try it out, the problem I ran into previously when attempting to filter was that half my formula is YoY growth and the other half is QoQ growth, so if I filter relative to quarter, I end up breaking the YoY leg, if I filter by year then I have minimum 8 quarters showing.