Utilisation and price per day

[Luka-enterprise]

I have agreements:

ID | Type | Check out | Check-in | Rental value | additional value | Total value

1 | A | 1.11.2020 | 5.11.2020| 500€ | 100€ | 600€

2 | B | 2.11.2020 | 9.11.2020|400€|0€|400€

I need to get the price per day (which is easy to divide by start and end, but how to create a calendar with revenue per day that will show me

1.11. 100€ /2.11. 150€ /3.11. 150€ /4.11. 150€ /5.11. 150€0/ 6.11. 50€...

How to show Utilisation that I have used cars 

1.11 1 / 2.11. 2/ 3.11 2/3.11 2/4.11 2/ 5.11. 2 / 6.11. 1....

when I found out the logic to put utilization by the date I need to sort it by the hour as I have check-in and check out time.

I'll add some real data later - just need to sort it out from 380.000 records 🙂

Thanks

[chrismarlow]

Hi,

You are going to need to do some work to get the dates in a calendar table you can then use the interval match on & work out what you want to do with the fields that dupe up, but I think this gets you part of the way; 

days:
load * Inline [
date
1/11/2020
2/11/2020
3/11/2020
4/11/2020
5/11/2020
6/11/2020
7/11/2020
8/11/2020
9/11/2020
];

data:
Load
	[Total value]/([Check-in]-[Check out]) as daily,
	*
	;
load * Inline [
ID | Type | Check out | Check-in | Rental value | additional value | Total value
1 | A | 1/11/2020 | 5/11/2020| 500 | 100 | 600
2 | B | 2/11/2020 | 9/11/2020|400|0|400
] (delimiter is '|');

Join IntervalMatch ( date ) 
LOAD 
	[Check out],
	[Check-in]
Resident data;

drop table days;

I've changed your dates & dropped your EUR symbol as well for ease (for me).

Let me know if it does not help.

Cheers,

Chris.

[Luka-enterprise]

Thanks,
I'll try to do it as you describe.

This is my table (data in attach)

AGR No

AGR Type

REASON

AGR Status

AGR Out Date

AGR In Date

AGR Branch Out

AGR  Branch In

UNIT_NO

Courtesy

Req Group

Car Group

Rental Value

The goal is to get all dates by each AGR Type and Reason that AGR Out and In have with having in mind we need to have a value of branch, unit, and car group and as a cherry on top Value need to have daily Amount.

Is this possible to do?
BR