topic Revert YTD numbers in QlikView

Revert YTD numbers

Thu, 08 Oct 2009 01:28:01 GMT

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.

Thank you!

Revert YTD numbers

Thu, 08 Oct 2009 01:54:53 GMT

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.

Revert YTD numbers

erichshiino — Thu, 08 Oct 2009 02:06:41 GMT

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