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

Question & 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.

4

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.

5

Hi I am trying to do a similar thing and am having a similar problem.

I am following this blog to make a S3 storage lens dashboard Create a custom Amazon S3 Storage Lens metrics dashboard using Amazon QuickSight | AWS Big Data Blog

I’ve tried the following and I’m always getting VISUAL_CALC_REFERENCE_MISSING

lastValue(
    ifelse({metric_name} = 'StorageBytes', {metric_value},0),
    [{report_date} DESC],
    [{aws_account_number},{aws_region},{bucket_name},{configuration_id},dt,{metric_name},{metric_value},{record_type},{record_value},{report_date},{storage_class},{version_number}]
)

lastValue(
    ifelse({metric_name} = 'StorageBytes', {metric_value},0),
    [{report_date} DESC],
    [{metric_name},{metric_value},{report_date}]
)

lastValue(
    ifelse({metric_name} = 'StorageBytes', {metric_value},0),
    [{report_date} DESC]
)
6

Ended up doing this: How to get only the last records in quicksight? - Stack Overflow

7

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.

8

Just this (KPI)

Screen Shot 2022-11-22 at 5.31.44 pm
Screen Shot 2022-11-22 at 5.31.44 pm1742×258 16.5 KB

9

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.

10

Hey David,

I’ve instead opted for KISS and just done a date range as the date is generated daily.

Thanks for your help though