"table calculation attribute references are missing in field wells" Error when using desired grouping fields

Hello, I’m getting this error frequently when I try to create the desired plots in my Quicksight dashboard. One example of this is below, but it’s happened to me with many plots.

I would like to have the map visual that will show me the active users per country, per game. I selected the “Points on Map” plot and put Country in the Geospatial field, Actives in the Size field, and Game in the Color field. With these settings I keep getting the table calculation attribute references are missing in field wells error. The SourceErrorCode under More Details is VISUAL_CALC_REFERENCE_MISSING.

Strangely enough, if I replace Game with Date in the Color field, the error is resolved and the map does display data. But this is not the breakdown that I want. I have a date filter applied to the dashboard, and I want the actives value by country & game to be filtered by the date field, but not broken down by dates in the plot itself.

What am I doing wrong here? Thank you!

Hello @jovanaV, I was wondering what datatype your “Actives” field is returning? Are you trying to use a calculated field that contains a level aware calculation?
If so that seems to be the only field type I am having an issue entering into the Size field well. The calculation might need to be done in a different way to utilize that value within a Points on a Map visual.

Thanks for the reply. My “Actives” field is built using the distinct_count() function to count distinct user identifier. However, the same problem shows up when I try using sum of Revenue. This is also not limited to the map visual.

Just now, I tried to show the Total Revenue over a date range. I just wanted to show the total Revenue value as a KPI box with the date filter applied to it. I get the same error when the filter is applied. If I remove the filter, the visual works but then it’s showing me total Revenue over the entire dataset which is not what I’m after.

My filter uses parameters so that the user can choose their own date range. If I use a regular filter, I don’t have any of these problems.

Hello @jovanaV, I could be incorrect on this, but rather than making a calculated field with distinct_count(), could you try entering the field that you are using in that calculated field in the size field well and using the distinct count aggregation on it there? That might be accepted by the visual.

As for the date filter issue, I would recommend posting that as a new topic. I am imagining that is going to be an issue related to your date field that is being imported, how the parameter or control is built out, or something else. So we can keep these questions a bit more organized, can you post a new question with a little more detail about that issue?

Got it, I’ll do that!

The only reason I bring it up is because I think the two issues are related. For the Actives Map issue that I originally bring up – I have tried just adding the user identifier field and choosing distinct count in the field well, but the same error occurs.

However, if I change my filter from using parameters to using set dates as the start/end then my map works as intended and I am able to apply the filter to the map and visualize the actives per country per game without including the date field in any of the field wells. As soon as I change that filter back to using parameters, then I see the same error and it requires me to replace game with date in the Color field in the map visual in order to resolve the error and display the data. Ideally, I would like to be able to visualize that map with country and game breakdown while having my parameter-based filter applied to it, but I don’t know if this is possible.

1 Like

Hello @jovanaV, is it possible that the format you set for the parameter datetime values does not match your date time values in the dataset? Maybe you are including HH:mm:ss in the parameter value and it is creating an error on your dataset.

The date formats were different however I didn’t have problems with this in my other visuals.

My parameter format was YYYY:MM:DD and my dataset format for date was YYYY-MM-DD. I changed the parameter format to match the dataset format, but still getting the same error for the map.

My time series plot with the same date filter works as you’d expect, so I wonder if it is a weird limitation in Quicksight that requires you to include the date field in the field wells in the visualization if you are using parameters for the date filter?

Hello @jovanaV, that is definitely a possibility. As a workaround, you could try to replace one of the fields in your field well with a calculated field to handle the parameters and exclude the filter on that visual. Something like this:

ifelse({Date} >= ${STARTDATE} AND {Date} <= ${ENDDATE}, {Actives}, NULL)

Then instead of filtering by dates, you could add a filter for that calculated field, click “Does not equal”, enter an impossible value and select exclude nulls from the last dropdown. That might solve the issue!

Hello @jovanaV, did my response help you resolve the issue you were facing in QuickSight? Feel free to mark it as a solution if it worked otherwise, let me know what issue is persisting. If I don’t hear back in a few days, I can close this ticket for now. In that case, feel free to post a new topic so it will be back at the top of our activity log!