Distance and MPG

user07657
user07657 Member
in Archive

Hello,

 

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

vehicle

 

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?

 

Thanks! 

Comments

  • amnel
    amnel Domo Employee

    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?

     

     

  • user07657
    user07657 Member

    Please see the attached with sample data

  • RGranada
    RGranada Contributor

    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 Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • user07657
    user07657 Member

    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?

     

    Thanks!

  • RGranada
    RGranada Contributor

    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!

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
This discussion has been closed.

Categories