If I have a transaction table storing mileage per vehicle posted on a monthly basis (YTD value posted every month) throughout the year what would be the most convinient way to calculate mileage driven per month?
The January value is how many miles the truck ran in January. February value posted is January +February so I need to subtract January from it to get the mileage.
What I want to achieve is a regular bar/line graph with Year Month as dimension, displaying mileage driven per month.
Might as well add that I am using above(XXX,1) to deal with it right now when playing around but it's a bit annoying and my expressions tends to be pretty long and complex which I try to avoid.
you might solve it in the script. See this example:
tmpTrucks: LOAD Truck, DATE, Mileage as YTDMileage
FROM MILEAGE.xls (biff, embedded labels, table is Plan1$);
Trucks: LOAD Truck, DATE, if( month(DATE)<>1, (YTDMileage - previous(YTDMileage)), YTDMileage ) as Mileage resident tmpTrucks order by Truck asc, DATE asc; drop table tmpTrucks;
When the month if not january I calculated the Mileage as the YTD Mileage minus the previous month
