Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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))
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()));
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.
The set expression is the same, just a different expression.
sum( {$<MonthNumber={"<=$(vCurrentMonth)"}>} [Variable 1]/[Variable 2])
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
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))