I have data that is grouped by sales rep and month to show the number of appointments they created, had scheduled, and completed for any given month. Their completed appointment goal for that month is on each record as well. In my Analysis, I’ve created a table that shows each rep’s performance for the current month and I’ve been using the max value in order to calculate their “% to goal” for completed appointments for the month. This only works if the current month’s goal is higher than every previous month, which is not always the case, so I tried using firstValue to only grab the current month’s value. I’m getting a VISUAL_CALC_REFERENCE_MISSING error even though every value that is used in the calculated field is on the table as well.
Here was my original formula for the calculated field: {completed_pacing} / max({scheduler_goals})
completed_pacing is an integer and so is scheduler_goals.
This works fine and the table shows properly, but does not always give me the right pacing value, so here is my new formula: {completed_pacing} / firstValue({scheduler_goals}, [{scheduled_at} DESC])
The calculated field saves, but gives me the error and won’t display the table. Any ideas on what I need to change to fix this? I’m open to using other formulas as well, this one just seemed like it should be the right one to get what I want.
Yep, “scheduled_at” is already in the table, and I made sure to add all columns that are variables in any of the calculated fields being used, which is why I’m confused. Is there anything else that can cause this error?
If you create a calculated field with just firstValue({scheduler_goals}, [{scheduled_at} DESC]) and without doing the division, and add it to a table visual containing the scheduler_goals and scheduled_at fields, do you get an error? This will tell us if the division has anything to do with the error that you’re getting.
Edited the calculated field to just be firstValue({scheduler_goals}, [{scheduled_at} DESC]) and I’m still seeing the same error, so looks like it isn’t the division aspect.
I can get the firstValue calculated field to show when I put all of the values in the Group by section rather than Value, but once I add in the division, the table still shows but the values are blank.
I was able to change the calculated field to match by date rather than use firstValue and ultimately get the result I was looking for.
It seems like the firstValue function expects the measure field we are calculating the first value for to be aggregated. So if you would have used suppose the following formula then you would have not got the error :- firstValue(Avg({scheduler_goals}), [{scheduled_at} DESC]).
