Distance and MPG



i have a dataset that has consists of Vehicle gas fill up data.


date of gas fill up

odometer reading (miles) at time of fill up

gallons pumped

cost per gallon

total spent



I'd like to have a card represent by vehicle the calculated MPG, and the distance traveled.


This would be by month/quarter/year depending on the cards time selection.


Is this possible?




  • Hello,


    Assuming you have all the necessary data pieces - which it looks like you do -  this should be no problem. 


    The catch is if the data is in the correct structure to do this in a Beast Mode or if you have to transform the data a bit via dataflow first.


    Could you provide a sample of the data?



  • Please see the attached with sample data

  • Hi,


    I would make a transformation in a redshift dataflow in order to get the odometer value in the last reffil.

    Like this :


    Select *,NVL(LAG("odometer mileage") RESPECT NULLS OVER (PARTITION BY vehicle ORDER BY date),0) PreviousOdometer from tst_dojo_gas 


    Then you can in a BeastMode get the Distance Traveled :


    `odometer mileage`-`previousodometer`


    And from here get the MPG also :


    `gallons pumped`/(`odometer mileage`-`previousodometer`)


    Sending as an attachement the export of the result table


    Hope it helps.



  • Ricardo,


    Thank you for your reply!  It certainly got the wheels turning and i appreciate the assist.


    Would this be something possible to perform in Magic ETL?


    Also, does this need to be done in dataflow or any possibilities within beastmode/card design?



  • Hi.


    I don't know if window functions can be used in Magic ETL. And i can't see any way for a bestmode to get previous record values...


    But we never know!

This discussion has been closed.