Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this, it seems to work:
sum({1<IDStatusReservación*={'1','2','3'}, //Sum of sums
Reservaciones.EsActual={0} ,
Sist.MesID={'38'},Reservación.MesID={'38'},
Reservación.Semana=,Reservación.Mes=,Reservación.Año={2014},Sist.Año={2014}>}aggr(
if(Only({1<IDStatusReservación*={'1','2','3'}, //Sum of sums
Reservaciones.EsActual={0} ,
Sist.MesID={'38'},Reservación.MesID={'38'},
Reservación.Semana=,Reservación.Mes=,Reservación.Año={2014},Sist.Año={2014}>}Sist.Fecha) =
max({1< IDStatusReservación*={'1','2','3'}, //Max Sist.Fecha for each
Reservaciones.EsActual={0} ,
Sist.MesID={'38'},Reservación.MesID={'38'},
Reservación.Semana=,Reservación.Mes=,Reservación.Año={2014},Sist.Año={2014}
>} total <idreservacion, Noches.Año,Noches.Dia > Sist.Fecha),
sum({1< IDStatusReservación*={'1','2','3'}, //sum of 1's
Reservaciones.EsActual={0} ,
Sist.MesID={'38'},Reservación.MesID={'38'},
Reservación.Semana=,Reservación.Mes=,Reservación.Año={2014},Sist.Año={2014}
>} Nights)
)
,idreservacion, Noches.Año,Noches.Dia,Sist.Fecha))
//
HTH
Best,
Sunny
Would you be able to share a sample application where you are trying to do this? Without data, it would be difficult to know what will work and what wont.
Best,
Sunny
Not sure if this will work for you or not, but can you try aggregate with NODISTINCT:
sum({1}Aggr(NODISTINCT
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))
Your set expression will only be evaluated once per chart, not per dimension value, hence you will get the same date for all idreservations.
I think you can use advanced aggregation / aggr() function in combination with FirstSortedvalue() and a set expression that limits to a date range, not a single date:
=Sum({<[Sist.Fecha (modified.date)]={"<=$(=max([Sist.Fecha (modified.date)]))"} >}
Aggr(
FirstSortedValue(
{<[Sist.Fecha (modified.date)]={"<=$(=max([Sist.Fecha (modified.date)]))"} >}
[Sum(Nights)] ,-[Sist.Fecha (modified.date)])
,idreservation)
)
See also attached sample file.
or may this is what you want?
If yes, I am attaching the qvw for reference.
Best,
Sunny
Thanks @sunindia and @swuehl, but I'm afraid I did not explain correctly. I don't have one aggregated valur. I need to sum values. In the table in my Opening Post i entered Sum(Nights). And that's a sum of N "1", one for each Night.
The granurality of the table is by date, for example reservation 10:
idreserv | ModifDate(Sist.Fecha) | Nights | Date of Stay | NightofStay |
10 | 05/may/2014 | 1 | 27/feb/2015 | 1 |
10 | 05/may/2014 | 1 | 28/feb/2015 | 2 |
10 | 05/may/2014 | 1 | 01/mar/2015 | 3 |
10 | 05/may/2014 | 1 | 02/mar/2015 | 4 |
10 | 05/may/2014 | 1 | 03/mar/2015 | 5 |
10 | 29/may/2014 | 1 | 27/feb/2015 | 1 |
10 | 29/may/2014 | 1 | 28/feb/2015 | 2 |
10 | 29/may/2014 | 1 | 01/mar/2015 | 3 |
10 | 29/may/2014 | 1 | 02/mar/2015 | 4 |
10 | 29/may/2014 | 1 | 03/mar/2015 | 5 |
10 | 29/may/2014 | 1 | 04/mar/2015 | 6 |
10 | 31/may/2014 | 1 | 27/feb/2015 | 1 |
10 | 31/may/2014 | 1 | 28/feb/2015 | 2 |
10 | 31/may/2014 | 1 | 01/mar/2015 | 3 |
10 | 31/may/2014 | 1 | 02/mar/2015 | 4 |
10 | 31/may/2014 | 1 | 03/mar/2015 | 5 |
10 | 31/may/2014 | 1 | 04/mar/2015 | 6 |
10 | 31/may/2014 | 1 | 05/mar/2015 | 7 |
So, you see, there's N firstsorted values for Nights, so that's why I want to sum nights for the first sorted value of Modified Date.
I need to have it like this because I want to be able to calculate occupancy for each day or month, in this example, they have nights in February, and nights in march. If I aggregate in the script it would be hard to calculate which dates belong to what month.
Note:
I don't have the field "NighOfStay" I entered it to better explain the table. In may 5th, they made the reservation. In may29th they called asking for one more night, and on may 31st, a seventh night.
Let me create a qvw. But I'm afraid I'll take a while.
May be this in that case:
=FirstSortedValue(Aggr(Max(NightofStay), idreserv, ModifDate(Sist.Fecha)), -ModifDate(Sist.Fecha)) or
=FirstSortedValue(Aggr(Sum(Nights), idreserv, ModifDate(Sist.Fecha)), -ModifDate(Sist.Fecha))
There's section access applied to the QVW, so I can't open it. Please update the QVW with section access removed.
If possible, try reducing the size, too (e.g. by reducing the data after selecting a subset).
There we go.
Here's a new one, also with Section Access but here's the logons:
USER, sunindia
USER, swuehl
It was already reduced but I'm sending it a bit more reduced now.