Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andrea_reyes
Contributor III
Contributor III

Accumulated without dimension of the month

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

Prueba.PNG.png

Appreciate the help that you can give me.

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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

View solution in original post

5 Replies
JonnyPoole
Employee
Employee

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

Capture.PNG.png

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

andrea_reyes
Contributor III
Contributor III
Author

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
Employee
Employee

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

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

Capture.PNG.png

maximiliano_vel
Partner - Creator III
Partner - Creator III

Hello Jonathan I have a almos the same problem

QView.png

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
Employee
Employee

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