Set analysis - Month data

[Anonymous]

Hello, guys,

I am new to QlikView and appreciate any help. I have got a problem with a kind of set analysis. My data is the following:

Date,

Car,

KilometersInTown,

KilometersOutOfTown,

KilometersHighway,

Driver,

StartingKilometers,

EndKilometers,

TotalKilomertersForItinerary

I need to calculate filtered data by month. The formula for StartingKilometers is:

=sum(StaringKmofCar) +

RangeSum(Above(total Sum(KmCity) , 1, 100))  +
RangeSum(Above(total Sum(KmHwy) , 1, 100)) +
RangeSum(Above(total Sum(KmOutofCity) , 1, 100))

StartingKmofCar is a field taken from the database.

Here is an example. The date is for the previous month of analyzing period.

Date	Car	KilometersInTown	KilometersOutOfTown	KilometersHighway	Driver	StartingKilometers	EndKilometers	TotalKilomers
30.06.2017	Volvo	10	12	5	John	1000	1027	27
30.06.2017	Volvo	0	0	80	John	1027	1107	80

I would like to take the end km of last period as starting km for the analysing period (in example - July 2017). Analysing period data:

Date	Car	KilometersInTown	KilometersOutOfTown	KilometersHighway	Driver	StartingKilometers	EndKilometers	TotalKilomers
01.07.2017	Volvo	1	2	3	John	1107	1113	6
02.07.2017	Volvo	4	5	6	John	1113	1128	15

I think that a possible solution is to save in aggregated dynamically table the EndKilometers and then to take the value as a StartingKilometers for the analyzing period. But I cannot find the right way to do this. What do you think? Is there any other possible solution?

Thanks,

Todor Vasilev

[tomovangel]

Hello, Could you provide sample data?

Maybe if you use a set expression stored into a variable vStartKM, it would be easier?

-Angel

[Anonymous]

Thanks Angel.

I have done it in other way.