Help with Quicksight Calculated Field for Timeseries aggregate Values

0

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.

timestampdevicesensorNamesensorValue
1612828800001device1modeRunning
1612828800001device1fuelUsed23
1612828800011device1modeRunning
1612828800011device1fuelUsed33
1612828800021device1modeRunning
1612828800021device1fuelUsed50
1612828800002device1modeNot Running
1612823800002device1fuelUsed45
1612828800002device2modeRunning
1612823800002device2fuelUsed77

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?