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

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

Thu, 03 Mar 2011 18:41:32 GMT

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.

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

Mon, 07 Mar 2011 13:21:44 GMT

Hi,

Your formula maybe could be expressed like as follow:

round(min(([stock price] * [stock quantity]),round(count(distinct Date)*(1-v_ConfidenceLevel)))*sqrt(v_YearProjection),.01)

For Understanding, count(PnL) is the same than count(Date), I suppose it's the number of elements, isn't it?

I hope it helps you.

Regards.

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

Mon, 07 Mar 2011 18:31:43 GMT

Hi Miguel,

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 ?

Thanks

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

Tue, 08 Mar 2011 10:50:34 GMT

Hi,

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.

Regards.

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

Tue, 08 Mar 2011 11:14:41 GMT

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

Is this even possible in qlikview?

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

Tue, 08 Mar 2011 11:31:04 GMT

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/8883.varapp.zip:550:0]

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.

Look forward to your solution.

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

Thu, 10 Mar 2011 16:57:21 GMT

Hi again,

I've checked out the example and I have some questions:

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

Is the PnL used from the table Portaflio or from Calculated PnL?

Is the given date from a filter on Date field or using a input field?

Regards.

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

Thu, 10 Mar 2011 17:14:10 GMT

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.

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

Oleg_Troyansky — Thu, 10 Mar 2011 17:21:54 GMT

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.

hope it helps

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

Thu, 10 Mar 2011 18:20:11 GMT

Hi -

That clarifies our confusion. We are trying to solve the problem using a reusable formula.

Is there a function in qlikview to get the total no. of distinct rows for a given column. We are using the formula below

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

where it fails with value 0.

This however works fine if the above formula is substituted with a specific number -

=-1*sum(Return * StockQty) * (5=Rank(sum(Return * StockQty)))

Return, Date, StockQty are valid loaded values.

ConfidenceLevel is a variable with a value between .9 and 1 (picked up via a slider).

Please help

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

Fri, 11 Mar 2011 07:58:13 GMT

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.

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

Fri, 11 Mar 2011 13:00:41 GMT

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.

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

Sat, 12 Mar 2011 13:47:20 GMT

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.