Not sure if this is possible so would like some opinions

[#1]

First some background...

I have some data in a straight table that is crunching some numbers using account, balances, rates, currencies and so on. All this is at a very detailed level (account, currency) and my users would like to see the data aggregated to a country level (one level higher). Unfortunately, due to things like inputfields, and calculations based on that for accounts, rates, etc, I can't just aggregate whats in the table as I need all of the detail there for the formulas.

So I thought about trying to create a copy of that table and try to aggregate up to the country level, but none of the expressions work, as I have to get rid of some of the more detailed columns to make it work, so they return nothing.

So I was really stuck until I remembered that I have a macro to clear out all of the inputs in an input field, such as:

sub Clear

     set x to ActiveDocument.Fields("Balance")

     x.ResetInputFieldValues 0, x

end sub

And if got me thinking... If a button could identify a column on the sheet, and therefore set its value, then there must be a way to pick up those values. And then I was wondering if I could use the macro to scope up the values I need at the aggregated level I need, and then spit them back out into another chart. Is this possible? Maybe not exactly like that, but in some way?

I don't have a lot of experience in Macros, nor Macros in Qlikview, so I am not all sure what I can do with them, and how objects can interact with them.

Thanks for your advice!

[JonnyPoole]

If you are working with INPUTFIELDs would the following help you aggregate just the INPUTFIELDs in a chart expression?  (from the help - below).   Or are you well past this in your attempts and research ?

Special Input Field Aggregation Functions

There are some special aggregation functions for input fields:

inputsum (inputfield[, distribution_mode][set_expression])

inputavg(inputfield [, distribution_mode][set_expression])

inputsum (inputfield[, distribution_mode][set_expression])

Returns the aggregated sum of inputfield iterated over the chart dimension(s). Inputfield must be a field name of a field properly declared as an input field in the script. 

When this aggregation function is used as expression in a table chart, it will be possible to edit the aggregated sum interactively. When hovering over the expression cell you will see an input icon. Clicking on the icon sets the cell in input edit mode. It is possible to use up/down arrow keys to move between cells while staying in input edit mode. The change in the aggregated sum will be distributed to the underlying field values using the chosen distribution_mode. The entire QlikView document will automatically recalculate whenever new values are entered.

The distribution_mode parameter can have the following values:

'+'	Default mode. Equal parts of the change are distributed to all the underlying values.
'*'	The change is distributed proportionally (to existing values) to the underlying values.
'='	The value entered is given to all the underlying values.
'/'	The value is divided equally between underlying values, without regard to previous distribution but keeping previous sum.

The distribution_mode values above can be amended with the following modifiers:

T	E.g. '+T'. Will cause a compensation of the entered change over the other selected values in the input field (keeping the grand total intact).
A	E.g. '+A'. Will cause a compensation of the entered change over all other values (keeping the grand total intact).

[#3]

Well, the problem is that I have expressions at the currency level in the same chart, so imagine something like this:

Account          Country          Currency          Bal

001                 USA               USD               1000

001                 USA               GBP               1000

I need this to appear as

Account          Country          Bal

001                 USD               2000

The problem is, in order to get the balance amounts, there are all sorts of expression formulas involved to get it, rates, etc, specific to the Currency. And since its all done on the fly, I cant get he Bal for Country without all thats done with the Currency.