setting the calculated dimension from a table chart to an array variable
Hi all,
I have a simpl table chart with columns Date and PnL. The PnL is calculated as a sum of (stock quantity * stock price) for a list of stocks for each date. I want to be able to refer to the PnL column list of values into an array variable that can be reused for doing a calculation to a single value. The formula is
Modifying the calculation has a glitch as we need to refer to the sum(stock_quantity * price) for a list of stocks on a given date. The PnL calculated column does that for you.
Is it not possible to refer to a calculated column directly ? How best to do this ?
Refer to a calculated column directly depends what are you exactly looking for, if you could attach a file with a detailed example (what is the expected result) maybe I could help you.
I would say, Miguel, that the issue is a table chart has been created from a mathematical operation on a previous table chart. Now, we wish to take this new table chart as an array and enact a formula on it, to derive a single value. For example:
[New table chart value1, New table chart value2.....]=[Old table chart value1, Old table chart value2...]*Value
What we want now is:
New Single Value = function([New table chart value1, New table chart value2....])
I have attached the application that we have done so far in a zip file with the data set. There is one table (Portfolio PnL) that is pre-calculated as part of script load and therefore doesn't change with stock or date selections. The VaR calculation is based on the PnL column in (Portfolio PnL) table.
However, there is another table Calculated PnL with a PnLCalc column that is an expression of sum(Return * StockQty).Unlike the PortfolioPnL table, the Calculated PnL table changes with stock or date selections.
What we need is to refer to the PnLCalc column in Calculated PnL and include that in our VaR calculation, which basically is to get to a PnLCalc value for a given date based on a function. More about this on James Kniep's post.
Hope this explains in detail what we are trying to do.
Judging by your question - i don't think you have understood our problem.
In the formula - PnL refers to Portfolio table.
It can't be the Calculated PnL table - as the column is calculated. We ideally want to refer to this calculated column in the CalcPnL table.
Don't understand your second question. The Date is never filtered unless you select one of the date's in the table. There is no input box for a date. They are all loaded from the data files.
In order to be clear and avoid confusion, let's stick to the common terminology - when we say "Table", we typically refer to a Database Table, as opposed to sheet objects like Table Box or a Straight Table Chart.
If you have an Expression (not Calculated Dimension!), the result of the Expression can be referenced within the same chart, but not in another chart. Within the same chart, an Expression can be referenced either by Name (Expression Label) or by number, using function Column(nnn).
Outside of the same chart, the results of an Expression cannot be re-used - you'd have to repeat the same formula again, and QlikView will recalculate it in the context of the given Dimensions.
