Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

How to calculate kilometer consumed through odometer?

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         

3 Replies
jstensig
New Contributor III

Re: How to calculate kilometer consumed through odometer?

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;

Not applicable

How to calculate kilometer consumed through odometer?

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

jstensig
New Contributor III

How to calculate kilometer consumed through odometer?

try the above syntax and see if it works :-)

Community Browser