Calculating mileage in a month

[ismabg88]

Hey everyone,

I want to calculate the number of kilometers done in a month. Since i don't necessarily have the mileage on first and last day of the month, I want to take the most recent mileage before the month selected and the most recent mileage after the month selected and substract them.

My problem is that i don't know how to select the first mileage before and after a selected month.

Here's an exemple to make it clearer

CAR_ID     Date               Mileage

01               27/02/2018     1000

01               02/03/2018     1100

01               15/03/2018     1300

01               04/04/2018     1800

01               14/04/2018     2000

So in this case, i want to calculate the number of kilometers in mars. I'll take the mileage of 27/02/2018  and 04/04/2018 and do 1800-1000.

I also want to put a limit to how far the selected dates can be: in mars, you can't take the mileage of january for exemple.

The month is selected throught a list box.

Thanks in advance !

[dplr-rn]

How do you want to display it? is it a Text Box or table?

depending on that you may need different approaches

[ismabg88]

I want a table with The car_id and the kilometers.

[dplr-rn]

Explain scenario in more detail and/or please

Give a sample output you desire (make in Excel)

Would help getting question answered quicker

Thank You

Dilip Ranjith

Sent via mobile

[chrismarlow]

Ismahane,

You might need to think about what to do when you have cars with no dates (like 02 that I added to your sample data, deleting the row in Feb), but otherwise the following might help;

1) Adding MTH or similar for your list box (you may want to do Month/Year)

2) Using following;

firstsortedvalue({<MTH=, Date={'<$(=Min(Date))'}>} Mileage,-Date)

firstsortedvalue({<MTH=, Date={'>$(=Max(Date))'}>} Mileage,Date)

Then you can do the difference.

Cheers,

Chris.