setting the calculated dimension from a table chart to an array variable

[#1]

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

=

round(min([PnL],round(count([PnL])*(1-ConfidenceLevel)))*sqrt(YearProjection),.01)

where ConfidenceLevel and YearProjection are input values from a slider.

Many thanks is advance for your solution.

[#2]

Hi,

Sorry but I don't understan your problem yet, one thing is true, you can't reference a column value from another chart, like Oleg said, you have to repeat the expresion.

So, one thing is clear, you need the value PnL from the Portaflio table, ok, but the formula at the first post, the formula in the zip file and the formula in the sanjeevi_n post aren't the same, so, I don't have a clear idea what are you looking for, could you attach a excel with the expected result and explaining it clearly?.

Regards.

[#3]

Hi,

Perhaps I can clarify the question a little more, as I have been working with sanjeevi_n on the same project. Please find attached an Excel version of the dashboard which we are trying to replicate in Qlikview.

Our ultimate goal is reproducing the Hist. VaR calculation you will see just to the left of the chart. This is essentially done by taking the nth ranked value (n being determined by a user input) of the portfolio returns. The formulae that have been introduced are intended to do just that.

We understand now that it is impossible for variables declared outside a chart to reference calculated values in a chart, but now we are having trouble with the latest formula, which is being used within the chart where the values are created:

-1*sum(StockQty*Return) * (Rank(sum(Return * StockQty))=round(count(distinct Date)*ConfidenceLevel))

This formula is implemented in the chart where we calculate the portfolio values, represented by sum(StockQty* Return). The formula will work fine if there is a number on the RHS of the equation, but when we substitute these non-numerical terms (which we have tested, and produce a proper number), we get the wrong answer.

I hope this answers your questions, so you can hopefully answer ours.

[#4]

Hi,

Here you are a possible solution, I've tried do the same as like excel file.

All is almost done, but, there is one thing I couldn't do, calculating the Percentile, I've tried with the function rangefractile but I think that isn't what are you looking for (additionaly it doesn't work like rangesum does), so, you have to find the right expression.

For it works you have to load the data ordered by Date field, check the script.

There are some variables created for limiting the data and calculating the date according to the user selection.

I hope it helps you.

Regards.