how to calculate a value using dates filtred by a calendar.

[momofiore]

Hey gays:

i had a table with 3 field, car id , DATE and mileage and i would like to calculate the substruction of mileage between two dates selected by a calendar. or the nearest dates to the selected one.

  car id               DATE                    mileage

CQ863NW	02/07/2018	68963
CQ863NW	26/07/2018	70085
CQ863NW	02/08/2018	70356
CQ863NW	07/08/2018	70569
CQ863NW	27/08/2018	71389
CQ863NW	04/09/2018	71842
CQ863NW	05/09/2018	71902
CQ863NW	10/09/2018	71947
CQ863NW	13/09/2018	72315

for example:

if i select by a calendar: 13/09/2018  and 10/09/2018  i will get 72315-71947 = 368.

i use two variables to store the date selected by 2 calendars.

or the nearest date selected:

for example:

if i select by a calendar: 14/09/2018  and 9/09/2018  i will get 72315-71947 = 368. becaus the r the nearest.

i attached a excel file.

[momofiore]

@youssefbelloum

[Vegar]

It could look something like this:

Sum( {<Date = {"$(=max({<Date={"<=$(varMax)”} >} Date)"}>} Milage)

-

Sum( {<Date = {"$(=min({<Date={"<=$(varMin)”} >} Date)"}>} Milage)

I personally depreciate using variables for date picking in my apps hence I loose important Qlik association (Green Grey White) by not using selections in the date fields.

[momofiore]

@Vegar Lie Arntsen

thank Vegar. i tried your answer but i still get errors regarding the expression.

here is my file. i created 2 variables varMin and varMax toget the dates from the calendar. !

[Vegar]

I'm a bit busy today, but I look at it a bit later. Maybe someone else in the community are able to help you adjust the SET in the meanwhile.

[momofiore]

i hope  that someone helps vegar.lie.arntsen‌ . thabk u all

[Vegar]

This expression will do the trick.

=Sum( {<DATE = {"$(=max( {<DATE = {"<$(=date($(varMax)))"}>} DATE) )"}>}MILEAGE)

-Sum( {< DATE = {"$(=min( {<DATE = {">$(=date($(varMin)))"}>} DATE) )"} >}MILEAGE)

[Vegar · Accepted solution]

Another solution where you don't need to use the variables is to let the calculation be based on your your highest and lowest MILAGE per car in your selection. I assume that the MILEAGE always increases and never decreases per car.

MAX(MILEAGE) will always represent the latest datapoint per car

MIN(MILEAGE) will represent the earliest datapoint per car.

sum(aggr(MAX(MILEAGE)-Min(MILEAGE),[CAR ID]))

[momofiore]

thanks vegar !!