I’m trying to use calculated field with the FistValue function in a table visualisation and I get “sourceErrorCode: VISUAL_CALC_REFERENCE_MISSING”. I’ve read other posts and it’s said that all the fields used in the calculated should be added to a group by in a table visualisation. But even after that I’m having the same error. The execution_status is a String value. Out of curiousity I tried to add count(execution_status) and the error was gone. How is it possible then to use FirstValue/LastValue with a string?
If there are any other approaches of getting the latest execution_status across the fields I’m using in a group by, please do let me know!
Hi @ksenstad,
Normally when you receive that error message, it’s because the visual requires one of the fields to be added that is being used in your calculated field. Could you share a screenshot of your visual along with the fields being utilized?
Yes, I added all the fields to the group by (attaching the pictures). As I mentioned above - as soon as I add count() to the {execution_status} in a calculated field, the error is gone, but I don’t need count, I need the string as it is.
Hi @ksenstad,
It looks like you need to add ‘execution status’ to your group field well, that should allow your table to populate results. Then if you prefer that field to not be in the table, you can hide it.
Adding execution_status worked, thank you. The only thing is that I realised the calculation itself is not correct and it’s not what we would like to achieve.
Imagine that we have some lambda_function_name. Each row is basically the data about lambda execution and it has start_time, execution_status, execution_id, execution_duration, etc. Each lambda_function_name has multiple rows (multiple executions). In the table we would like to display data like this (attaching a screenshot) + add the latest execution status and latest execution_id.
If we calculate the latest execution status using the calculated field above, it requires to add start_time, and status to the group fields. But if we add them, then the number of rows will not be one, but equal to the number of groups (e.g. some lambda has multiple executions and multiple execution statuses, and even if I hide the column, it is not what we want. we want one line with aggregation by account,lambda_function_name,sg_pipeline_name but not any other fields)
Hi @ksenstad,
What if you setup filters to only recognize the the first value in those groups?
My sample datasets are a bit different so I can’t replicate a great example but if you need additional assistance trying to setup filters to limit your values returned, it would be ideal if you could upload a copy of your analysis with anonymized data to the QuickSight Arena view!
Sorry for the late response. I’ve uploaded the sample dataset and also recreated the visualisation. The calculated field is called testcalc. You will see that the table has multiple rows and what I want is one line per lambda function with the following table fields:
account_name, lambda_function_name, sg_pipeline_name, latest_start_time (and I don’t want start time to be used in group fields, I was able to achieve that by using latest_start_time instead of start_time in testcalc), latest_exection_status
Hi @ksenstad,
Looking at the Arena view example; you’ll have to determine the execution status you’d like to filter by to only show one row as that’s causing the multiple lines for this case. If you setup a filter on the visual to only include one execution status, that will remove the others:
Since we have not heard back from you, I’ll go ahead and close this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.
