Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion Board for collaboration related to QlikView App Development.

Announcements

CUSTOMERS ONLY: Now accepting **customer** applications for the 2023 Luminary Program: **SUBMIT NOW**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Accumulated without dimension of the month

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

andrea_reyes

Contributor III

2014-12-03
07:05 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Appreciate the help that you can give me.

406 Views

1 Solution

Accepted Solutions

JonnyPoole

Employee

2014-12-04
02:52 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

237 Views

5 Replies

JonnyPoole

Employee

2014-12-03
09:11 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

237 Views

andrea_reyes

Contributor III

2014-12-04
08:51 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

237 Views

JonnyPoole

Employee

2014-12-04
11:06 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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

237 Views

maximiliano_vel

Partner - Creator III

2014-12-04
02:08 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

237 Views

JonnyPoole

Employee

2014-12-04
02:52 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

238 Views