Set analysis - Month data

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set analysis - Month data

tomovangel — Fri, 21 Jul 2017 08:13:07 GMT

Hello, Could you provide sample data?

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

-Angel

Re: Set analysis - Month data

Anonymous — Fri, 21 Jul 2017 08:32:52 GMT

Thanks Angel.

I have done it in other way.

topic Set analysis - Month data in QlikView

Set analysis - Month data

Re: Set analysis - Month data

Re: Set analysis - Month data