
Re: Sum 12 months data according to the selected period
Bala Bhaskar Jul 15, 2018 12:37 PM (in response to Diego Alejandro Velez Becerra)May be this:
aggr(RangeSum(Above(Sum({<Date = {">=$(=Max(Date)  11)<=$(=Max(Date))"}>} Units),0,12)), $(Var_Selected),Date)
Check this link:
Calculating rolling nperiod totals, averages or other aggregations

Re: Sum 12 months data according to the selected period
Diego Alejandro Velez Becerra Jul 23, 2018 3:51 PM (in response to Bala Bhaskar)Hi Bala Thanks a lot for your answer,
I learnt a lot reading the topic you shared with me, and tried to use your formula and some of the formulas in the other post, they worked somehow but were also giving some trouble to me when I was trying to show the data as I wanted to, also my period format "MMYYYY" and the fact that I needed to sum the full 12 months rolling, including this year and the previous one were also problematic at some moment.
So I ended up doing a formula that included all of the periods one by one with some kind of variations.It's not practical but did the trick.
Sum({<PERIODO={$(=Max(PERIODO))}>} [VALOR_CONSUMO_ACTIVA])
sums the selected period
+
if
(
mid(Max({$} [PERIODO]),5,2) 1 <= 0,
Finds out if the previous PERIOD to the selected one is in this year (ex for 201801 is 01 and the previous PERIOD would be 201712)
Sum({<PERIODO={$(=(Max(PERIODO)89))}>} [VALOR_CONSUMO_ACTIVA]),
if the previous PERIOD to the selected one is in the past year adds the value for this PERIOD , this situation only happens when the selected period is YYYY01 then the difference will always be 89 and will grow by 1 with each period on the formula.
Sum({<PERIODO={$(=(Max(PERIODO)1))}>} [VALOR_CONSUMO_ACTIVA])
if the previous PERIOD to the selected one is in this year then just rests 1 to the selected PERIOD.
)
and it keeps going until the 12 PERIODS.
+
if
(
mid(
Max({$} [PERIODO]),5,2) 2 <=0,
Sum({<PERIODO={$(=(Max(PERIODO)90))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)2))}>} [VALOR_CONSUMO_ACTIVA])
)
+
if
(
mid(
Max({$} [PERIODO]),5,2) 3 <=0,
Sum({<PERIODO={$(=(Max(PERIODO)91))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)3))}>} [VALOR_CONSUMO_ACTIVA])
)
I believe the formula could be shorter using a growing variable but I ain't familiar with that yet.
Thanks a lot again.

Re: Sum 12 months data according to the selected period
Bala Bhaskar Aug 13, 2018 10:28 AM (in response to Diego Alejandro Velez Becerra)Thank you, i'm glad for giving you at least a small hint, to solve your issue.



Re: Sum 12 months data according to the selected period
Anil Samineni Jul 24, 2018 2:39 AM (in response to Diego Alejandro Velez Becerra)Perhaps this?
Sum(Aggr(If(DateField>=Date(AddMonths(DateField,11)) and DateField<=Date(Max(DateField)),Sum({<PERIODO={$(=Max(PERIODO))}>} [VALOR_CONSUMO_ACTIVA])
+
if
(
mid(Max({$} [PERIODO]),5,2) 1 <= 0,
Sum({<PERIODO={$(=(Max(PERIODO)89))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)1))}>} [VALOR_CONSUMO_ACTIVA])
)
+
if
(
mid(
Max({$} [PERIODO]),5,2) 2 <=0,
Sum({<PERIODO={$(=(Max(PERIODO)90))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)2))}>} [VALOR_CONSUMO_ACTIVA])
)
+
if
(
mid(
Max({$} [PERIODO]),5,2) 3 <=0,
Sum({<PERIODO={$(=(Max(PERIODO)91))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)3))}>} [VALOR_CONSUMO_ACTIVA])
)),Dimension1,Dimension2))

Re: Sum 12 months data according to the selected period
Diego Alejandro Velez Becerra Jul 31, 2018 10:35 AM (in response to Anil Samineni )Hi Anil, sorry for not answering before I was in a little trip, I don't really understand the last answer you gave, I was thinking in something like
Sum({<PERIODO={$(=Max(PERIODO))}>} [VALOR_CONSUMO_ACTIVA])
+
For i = 1 to 11
if
(
mid(
Max({$} [PERIODO]),5,2) i <=0,
Sum({<PERIODO={$(=(Max(PERIODO)(88+i)))}>} [VALOR_CONSUMO_ACTIVA]),
Sum({<PERIODO={$(=(Max(PERIODO)i))}>} [VALOR_CONSUMO_ACTIVA])
)
Maybe you can help me to define that variable for that part of the formula so I can give you the correct answer, because you helped me a lot. Thanks again.
