topic How to calculate kilometer consumed through odometer? in QlikView

How to calculate kilometer consumed through odometer?

Thu, 03 May 2012 05:19:22 GMT

Hi,

What if you have this datasource

Date Vehicle Odometer

3/12/2012 A 12345

3/12/2012 A 20001

3/12/2012 B 43556

3/13/2012 B 49909

Can we able to get the kilometer travel per Vehicle? A and B?

Because, in my datasource, I only have one Odometer Reading, I'm not sure if it is reset to ZERO everytime it's newly fueled.

Thanks,

Bill

Re: How to calculate kilometer consumed through odometer?

jstensig — Thu, 03 May 2012 07:35:34 GMT

Hi,
Take a look at the Previous-function and use it in a resident load. Make a Resident-load on the initial data and remember to sort your data.

Syntax (UNTESTED)

// Get data
Initial_load:
LOAD Date, Vehicle, Odometer
FROM [datasource];

// Resident load, where you sort the data by Vehicle and Odometer AND uses the Previous-function to calculate the difference (trip)

Data:
NoConcatenate
LOAD *
,if(Vehicle=Previous(Vehicle),Odometer-Previous(Odometer),null()) AS Trip
RESIDENT Initial_load
ORDER BY Vehicle, Odometer;

// Drop initial table

DROP TABLE Initial_load;

How to calculate kilometer consumed through odometer?

Thu, 03 May 2012 07:51:46 GMT

Thanks for the idea.. Can u have an example?

How to calculate kilometer consumed through odometer?

jstensig — Thu, 03 May 2012 08:02:26 GMT

try the above syntax and see if it works 🙂