Sum Values in a month based on the last data of each element of the minimum granularity

[Anonymous]

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.

[sunny_talwar]

Which chart are we trying to fix here?

[swuehl]

I just used the first chart I've seen (Produccion on the lower left).

Not sure if this comes close to what you want. This sample is already quite complex, it would be better to discuss this with a stripped down demo dataset.

[Anonymous]

The two pivot tables below the line chart are the ones we are trying to fix. They are the same.

I was doing more work on the second one (in the middle).

There's a disabled expression Called "AA Bien sin Aggr". If you enable it, you'll see what's supposed to be Historic Data from same month Last Year.

I advice to select 2015 and May in the second calendar "Calendario de Reserv". Which are selling dates.

If you do so, you are supposed to see 2,428 Noches en any of the pivot tables. This are the nights we've sold/booked in May 2015.

What we want to see, is what did we book in 2014 may.

"AA Bien sin Aggr" Sums the nights that were booked 2014 may, as they were back then, but it sums them several times.

For example:

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

"AA Bien sin Aggr" sums 5+6+7+14+7+2+2 = 88

Then I tried the Max(Sist.Fecha) but only got 7+0+0+2 = 9

PS.1

idagencia should not be used.

If we use Aggr it should be by idreservacion or maybe by idreservacion,Sist.MesID

In this data set May 2015 is monthID = 50, so May 2014 is MesID 38.

Ps2

A few translations that might Help.

Fecha = Date

Mes = Month

Año = Year

Noche = Night

AA (Año anterior) = Last Year

Reservaciones.EsActual={0} Means we are looking at what's not actual value. We are looking at historical data.

PS3.

The result for this data set should say that historicaly in may 2014, we have booked something like 2000 nights, most of them for the following months of 2014, some for 2015, but nothing for 2016.

[Anonymous]

You know what should be our first approach?

Forgetting about set analysis and first try to get it in a more conventional way.

Select: 2015, may, Historic Data (0 as Reservación.EsActual). And try to get it like that.

Actually I attach the qvw with the selections and a few attempts I've tried.

In theory it should work like this:

sum( Aggr( [NODISTINCT] FirstSortedValue(
Sum(Nights),-Sist.Fecha)
,idreservacion))

But It seems FirstSortedValue, gives you a non aggregated value, so yoy cannot use sum(Nights).

The question remains. How to pick all the most recent values?

[sunny_talwar]

It is difficult to work with the data in the application because I don't have any understanding of the data, but for the aggregation part, you should be able to this:

=Sum(Aggr(NODISTINCT FirstSortedValue(Sum(Aggr(NODISTINCT Sum(Nights), Dimension)),-Sist.Fecha) ,idreservacion))

The inner aggregate might able to help you with your inner aggregation. Also I am not sure which aggregation will use NODISTINCT, but you can play around with a little.

HTH

Best,

Sunny

[swuehl]

I agree with Sunny that's pretty much guessing around, because it's quite hard to dive into your data model.

Something like suggested above by sunindia  should work, but I think a NODISTINCT is not necessary / should be avoided by selecting the correct aggr() dimensions (ok, this might be the hard part, see first line).

See attached for my suggestion.

[Anonymous]

Hi again! swuehl‌, sunindia‌

I've almost solved it! Now I only need to add set analysis no my expression which uses if and Aggr, but I need help

This worked with may 2014 selected and 0 as Reservaciones.EsActual:

sum(aggr(
if(Sist.Fecha = max(total<idreservacion, Noches.Año, Noches.Mes>Sist.Fecha),
sum(Nights)
)
,idreservacion,Noches.Año,Noches.Dia, Sist.Fecha))

I got the value exaclty what I needed, not one night more, not one night less: 3958 but of course I need set analysis, the idea es to compare my selected values to last years... so time to code the expression to get info for another month...

So I applied set analysis to each aggregation (sum, max and sum):

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(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))

BUT:

It works if I don't select anything, or if my selections do not traspose with my set analysis.

If I select 2015 as Reservación.Año (Year) it doesn't work.

I'm guessing I could use an alternate state, but maybe it's just something I'm missing, most likely related to the IF.

Long story short: How can I make the first simple expression work for another date?

Thanks!

If it helps, I attach the qvw with a tab called "Works" and the tables

With no set analysis which is not expected to work when I click on something else, and the one with set analysis that should work, but doesn't work properly.

:

[sunny_talwar · Accepted solution]

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

[Anonymous]

That's it!!

Thank you both for your time! Qlik Community is awesome!

For people who stumble upon this solution.

Later I placed the set in a variable called setAAMM (means Set for Last Year, Same Month) and my chart has this expression

sum( $(setAAMM) aggr(
if(
Only($(setAAMM) Sist.Fecha) = max( $(setAAMM) total <idreservacion, Noches.Año,Noches.Dia, Segmento > Sist.Fecha),
sum(  $(setAAMM)  ContadorNochesFuturo)
)
,idreservacion, Noches.Año,Noches.Dia, Segmento,Sist.Fecha))

I will later update when I make different sets and let the user decide the beheavior of the expression (acumulated, or quarter, or week of last year, or last month, etc)

[sunny_talwar]

Not a problem.

I am glad we were able to help.

Best,

Sunny