Imagine if we have a time series values like this, lets say the timestamps are in epoch and device is emitting a payload every minute.
| timestamp | device | sensorName | sensorValue |
|---|
| 1612828800001 | device1 | mode | Running |
| 1612828800001 | device1 | fuelUsed | 23 |
| 1612828800011 | device1 | mode | Running |
| 1612828800011 | device1 | fuelUsed | 33 |
| 1612828800021 | device1 | mode | Running |
| 1612828800021 | device1 | fuelUsed | 50 |
| 1612828800002 | device1 | mode | Not Running |
| 1612823800002 | device1 | fuelUsed | 45 |
| 1612828800002 | device2 | mode | Running |
| 1612823800002 | device2 | fuelUsed | 77 |
And we would like to know what was the TotalfuelUsed in the last 30 days when the mode was Running (mode Not Running means engine is on but not moving = idle). We tried using calculated field and periodToDateAvg function but somehow it is just not possible to do this complex query in one or multiple calculations
-
Calculated Field 1: Productive
Productive time = sum(periodToDateAvg({sensorName} = 'mode' and {sensorValue} = 'Running', Month )) <<<---- Need help here
-
Calculated Field 1: TotalFuelUsedDuringProductive
TotalFuelUsedDuringProductive = TotalFuelUsed/Productive
-
Expected Output
device1 TotalFuelUsedDuringProductive = (50-23)/(3-2)
Is this even possible to do in Quicksight? or do we have to use Athena SQL Query?
