sum values of last months

[jvargasmayorga]

Hello Community.
I have the following problem.
This is a sum of quantities table, grouped by time dimension (Year and Month)

Cant. Trx

Sum(Cantidad_transito)

What I need now is to add the values of the last 3 months, but for each month.
For June: You must add the values of April, May and June
For February: You must add the values December 2021, January and February. Something like that:

In this sum I am having problems. I use the following formula and it gives me the wrong result (same as a simple sum)
Qty trx 3 Month =>

sum({<Fecha_transito={">=$(=MonthStart(AddMonths(Max(Fecha_transito),-2)))<=$(=MonthEnd(AddMonths(Max(Fecha_transito),0)))"}>} Cantidad_transito)

Using this formula (adding  TOTAL), it only considers the sum of the last 3 months and that result places it in all the same months.

sum({<Fecha_transito={">=$(=MonthStart(AddMonths(Max(Fecha_transito),-2)))<=$(=MonthEnd(AddMonths(Max(Fecha_transito),0)))"}>} TOTAL Cantidad_transito)

I think the solution would be like NOT considering the time dimension in the sum, but showing it each in its own time, it's very confusing, any idea how to solve it? Need this result.

Thanks

[rubenmarin]

Hi, set analysis is calcualted before the table, it creates the subset of values used to calulate the table, so it's not affected by dimension values when doing the filter, that's why it always takes the last 3 months.

For that example you can use Before(), which retrieves the values of the previous columns, using TOTAL to ignore the dimensions above the last one, the 2nd parameter to start in the current row (0), and the 3rd paramater to tell how many columns to go back. All inside in a RangeSum() to sum all different values:

RangeSum(Before(TOTAL Sum(Cantidad_transito),0,3))

[jvargasmayorga]

Thanks for your answer.
It works perfect for me to add quantities.

But now I have a problem. I hope you can help me:

This is the data table, I have by year, month and plate (placa).

I am adding "cantidad_transito" but making a distinction by "placa", I use the following:

sum(aggr(only(Cantidad_transito),Placa,Año, Mes))

It shows me the following (good)

Now I want to make a sum of the last 2 months using distinct or only, it should have a result like this

I am using the following:

RangeSum(Before(TOTAL sum(aggr(only(Cantidad_transito),Placa,Año,Mes)),0,2))

or 

RangeSum(Before(TOTAL sum(aggr(only(Cantidad_transito),Placa)),0,2))

And I can't get the result I want

I hope you can help me.
Thank you

[rubenmarin]

Hi, that probably returns 7 (3 from may + 4 from jun) before only does the same calculation on the previous column, and to make the distinct count you need that each month acces the previous months on the same column.

To do this tehere is a soluton using an asof table, this way each month(column) has acces to the previous months, and you can do a count distinct on the dataset:

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

Having that solution applied the expression could be something like Count(Distinct {$<MonthDiff={"<3"}>} Placa)

[jvargasmayorga]

Hi. 

Good information.
I have read the whole post of "Te As Of Table", I have followed the instructions, but it continues to show me the same way, taking the distinct of each month and adding.

Then I have done another test to see if the tables indicated in the Post are being loaded correctly and I see that there is a field that is not loading correctly.

The "AsOfMonth" field is not loading good and that should be the same as "Month", it should show the same values

I think I'm close to having the solution, I hope you can help me with this case.
Thanks for your time.

[rubenmarin]

Hi, you can try adding asofmonth as the vertical dimension, you can split it in 2 fields to show year and month.

Each AsOfMonth has access to the previos months, so in june 2022 Jun is the only asofMonth related, the other months will have differnts asofmonths related, that's why it shows as null.

The backdraw of this technique is that you need to use the asofmonth calendar fields as dimensions

[jvargasmayorga]

Hello. I tried as you indicated, and I can't get the result. I thought I was close to the solution, I see it more difficult.
Do you have any other way to do it? I have several weeks trying. Thank you

[rubenmarin]

Hi, if you can upload a sample with some dummy data that represents the issue you have, I can take a look to create the expression.

[jvargasmayorga]

OK thank you for your time. I'll try to explain the best I can.

The initial context is as follows. I have a Fact, where I have a transit detail for each vehicle, the date, time, vehicle plate and the place of transit are recorded.
So the indicators that I have to show are:

A) Number of "vehicle plates" that travel each month -> It is like doing a Select distinct "vehicle plate" by year and month.

I use this formula. This works fine. No problem 🤙

sum(aggr(only(Cantidad_transito),PlacaVehiculo,[Año transito], Mes_transito))

B) Number of "vehicle plates" that traveled in the last 3 months -> It is like doing a Select dinstinct "vehicle plate" where Month <= "current month" and Month >= "3 months before".  (Here's tjhe problem)

For example:
March => Number of plates (distinct) from March, February, January
February => Number of plates (distinct) of February, January, December
January => Number of plates (distinct) of January, December, November.

I tried whit the nexts formulas.

Count(Distinct {$<MonthDiff={"<3"}>} PlacaVehiculo)

sum({<Fecha_transito={">=$(=MonthStart(AddMonths(Max(Fecha_transito),-2)))<=$(=MonthEnd(AddMonths(Max(Fecha_transito),0)))"}>} TOTAL Cantidad_transito)

sum( aggr( rangesum( above( sum(aggr(only(Cantidad_transito),PlacaVehiculo,[Año transito], Mes_transito)),0,2) ),Mes_transito))

And it doesn't do the calculation correctly.

BEst Regards

[rubenmarin · Accepted solution]

Hi, in the asogf table try adding fields for year and month:

[As-Of Calendar]:
Load Month,
  AsOfMonth,
  Year(AsOfMonth) as AOYear,
  Month(AsOfMonth) as AOMonth,
  Round((AsOfMonth-Month)*12/365.2425) as MonthDiff,
  Year(AsOfMonth)-Year(Month) as YearDiff
  Resident tmpAsOfCalendar
      Where AsOfMonth >= Month;

And instead of Año_Transito and Mes_Transito use this new fields as dimensions (AOYear and AOMonth).

With this the expression Count(Distinct {$<MonthDiff={"<3"}>} PlacaVehiculo) should work.