'firstValue' of a dimension sorted by timestamp and partitioned by another dimension

Question and answer
,
#1

My events have the following fields
[timestamp, instrument_name, event_type, …]

I have the following definition for a calculated field “latestInstrumentStatus”

firstValue
	(
	     {event_type}, 
	     [timestamp DESC  ],
	     [{instrument_name}] 
	)

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.

#2

Hello,
You need to include all your fields “event type”, “timestamp” and “instrument_name” in the Group_by Field.

#3

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.