Hello.
This is a hard question, or at least far from a basic one.
Requirement: Time Travel. "To position myself in any point of time and look at the data that what up to date at that time"
Extra info: For the non historic table we already have the important info: Booked nights (when where they reserved, which nights in calendar are they in), Amount paid and more).
We have modificationdate of course. I can pull out that data with set analysis... BUT I'm still not able to pick the "newest" record at that time for each reservation.
Either:
a) I end up summing all records and data multiplies, or
b) I try to get the latest date but I get only the max date for all of them.
Example of data:
| idreservation | Sist.Fecha (modified.date) | Sum(Nights) |
|---|
| 10 | May 5 | 5 |
| 10 | May 29 | 6 |
| 10 | May 31 | 7 |
| 20 | May 6 | 14 |
| 20 | May 8 | 7 |
| 30 | May 10 | 2 |
| 40 | May 31 | 2 |
What I'm using now:
sum({1}Aggr(
sum( {<
IDStatusReservación={'1','2','3'},
Reservaciones.EsActual={0} , //Historical Data
Sist.MesID={$(=vMaxReservMesID-12)},Reservación.MesID={$(=vMaxReservMesID-12)},
Reservación.Semana=,Reservación.Mes=,Reservación.Año=
,Sist.Fecha={$(=Max( {1< Sist.MesID={$(=vMaxReservMesID-12)} >} Sist.Fecha))} //It's giving me may 31 for all, even it's aggr by each idreservation
>} Nights)
,idreservacion))
What I'm getting:
For id 10 on may 31: 7
For id 10 on may 31: 0
For id 10 on may 31: 0
For id 10 on may 31: 2
TOTAL 9
What I should Get:
Most recent for id 10: 7
Most recent for id 20: 7 (which is the value on the most recent date for that id reservation, in may 8th)
Most recent for id 30: 2 (same as above)
Most recent for id 40: 2
Total 18
Note:
I could Aggr in script with Last Value, in a loop for each month... but they want to place themselves at any date of time. It may be May 7th, or the 8th.
