By using AWS re:Post, you agree to the Terms of Use
/QuickSight calculated field: How to get sum of last values of timestream measures/

QuickSight calculated field: How to get sum of last values of timestream measures



I have a TimeStream table containing power consumed by IoT devices. I would like to create a calculated field in QuickSight which computes the sum of last power values reported by all devices?

1 Answers

If I understand correctly, the query result is a single value which represents the aggregate power of all devices. The device values to aggregate is the latest power of each device. For e.g. if there are 3 devices with the following power values

device1: 00:00:00 val=20, 00:00:05 val=25, 00:00:10 val=20
device2: 00:00:00 val=19, 00:00:05 val=72, 00:00:10 val=81
device3: 00:00:00 val=00, 00:00:05 val=22, 00:00:10 val=100

The result will be (20 + 81 + 100) = 201

A sample query for this is below

WITH cte1 AS (
    SELECT device, first_value(measure_value::bigint) OVER (PARTITION BY device ORDER BY time DESC) as value FROM <db>.<table> WHERE time > ago(1m)
), cte2 AS (
   SELECT device, min(value) as value FROM cte1 GROUP BY device
) SELECT sum(value) FROM cte2

The time predicate e.g. ago(1m) is necessary to ensure that the whole table is not scanned. It can be set to any time window that will give the latest power values of all devices.

answered 4 months ago
  • Thanks Rajesh! I was thinking of doing this via introducing a calculated fields on quicksight? Is this use case supported?

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions