Accumulated without dimension of the month

[Anonymous]

Good afternoon,

I need to calculate the accumulated for a selected month, the accumulated is calculated by adding the indicator value (Variable 1 / Variable 2) of the previous 12 months, the problem is that I need to display the value in a text box where I don't have the dimension of the month to use the function rangesum.

Below I show a table with test data and the value you want to get to select the month December

Appreciate the help that you can give me.

[JonnyPoole]

In this solution, i added a numeric identifier to capture a MonthNumber for each month. I also set a variable to capture the month number of today's month  (December=12).

Then i can use an expression to bring back all values where MonthNumber <= vCurrentMonth as follows:

Sum(  {$<MonthNumber={"<=$(vCurrentMonth)"}>}  [Variable 1] )

LOAD

  RecNo() as MonthNumber,

  Mes,

    Indicador,

    [Variable 1],

    [Variable 2]

FROM

(ooxml, embedded labels, table is Hoja3)

where not (Mes='Total');

let vCurrentMonth=num(Month(Today()));

[Anonymous]

Hello Jonathan,

Thanks for your help.

The problem is that the value indicator is calculated dividing the variable 1 with variable 2 and Ican`t add the value of variable 1 to 12 months divided the value of variable 1 to 12 months because the result is wrong.

I need to add the value result of the indicator of each of the 12 months as can be seen in the excel

thanks again.

[JonnyPoole]

The set expression is the same, just a different expression.

sum(  {$<MonthNumber={"<=$(vCurrentMonth)"}>}  [Variable 1]/[Variable 2])

[maximiliano_vel]

Hello Jonathan I have a almos the same problem

In the image above the Accum(Val1/Val2) is calculated as fallows:

RangeSum(Above(Sum(Val1)/Sum(Val2), 0, 12))

What i need is to pick a Month (i.e. 10) and in a Text Object have the corresponding Value (for Month = 10, Value = 4.20)

Thanks in advanced for your kind help.

Best Regards

[JonnyPoole · Accepted solution]

With yours i needed to aggregate the val1/val2 results by month first and then add up the results.  I used a variable to keep track of the CurrentMonth .  I set it to 10 but you can set that dynamically by max(month) etc...

=sum( {$<Month={"<=$(vCurrentMonthNumber)"}>}aggr( sum( {$<Month={"<=$(vCurrentMonthNumber)"}>} Val1/Val2),Month))