The Idea is to get the latest “event_type” for each “instrument_name” and display it in a tabular format like below
InstrumentName
Latest Status
ABC
OK
DEF
ERROR
The table display(Group by column - InstrumentName, Value - LatestInstrumentStatus) visual tries to apply some grouping for the latestInstrumentStatus and errors out with the error message “VISUAL_CALC_REFERENCE_MISSING”.
Any suggestions on how to achieve this in QuickSight appreciated.
Hmmm… Not sure why I would need to do that. I only want to group by instrument_name. For the events pertaining to the instrument_name, I want to find out the latest value of the field event_type based on sorting by the timestamp field.
Any other solution to what I want to achieve is fine as well. I dont need to use the firstValue function. What I want is a simple way to display the latest event_type for each instrument_name.
Hi @krr, @JoseB-aws is correct. To workaround this, you can create your calculated field in your dataset and then you shouldn’t need to add the fields used to create your calculated field in the visual.
Can you take a screenshot of your field wells? All the fields present in your partition have to be in your field wells but you can hide the ones that you don’t want to show.
Let’s forget about the ifelse function in this calculated field for now. Can you describe what you’re trying to calculate? Are you trying to find the last value for {metric_value} for each {metric_name} based on {report_date}? If that’s the case, then the correct calculation is:
Last_metric_value = lastValue({metric_value}, [{report_date} ASC], [{metric_name}]
Create a table visual, and put metric_value, report_date and metric_name in “Group by”. Put Last_metric_value in “Value”. Verify that Last_metric_value in the table is correct.