Monte Carlo VaR calculation and aggregation in Pivot Table

[plamen_alexiev]

I am trying to calculate and aggregate MC VaR of some Portfolio with some Positions in Qlikview. On Reload I load Monte Carlo Series Data and it contains n Positions x nRuns Values.

The MC consist of two components: VaR = Mean - Confidence Value, where

Mean = Sum(Value)/nRuns, and

The Confidence value is calculate relatively complex in next steps:

1. Forms sums of selected positions grouped by Run Numbers;
2. Sorts sums ascending;
3. Fetches i-th sum, where

          i = (100 – Confidence) * nRuns / 100;

In Qlikiew, the Mean is calculated and aggregated simply: Sum(Value)/nRuns. The Conf.Value also is calculated simply:

=Min(Value,$(vIdx)),

vIdx holds i-th index,; it depends from Confidence.

But the Conf.Value is aggregated relatively complex. I am trying:

=Min(Aggr(Sum(Value),RunNumber),$(vIdx)).

Last formula returns correct results only on top aggregation level in Pivot Table and on aggregation level in Straight Table.

Please, help: how modify formula so it returns correct results on sub aggregation levels in many Dimension Pivot Table.

I am working with QlikView 11.12 version

[marcus_sommer]

Maybe this: Monte Carlo Methods is helpful for you in any way.

- Marcus

[plamen_alexiev]

Hello Mr.Sommer,

I know your post. But my problem is something other.

Another Calculate Engine is calculated Monte Carlo Series and saved in DataBase.

My QV application loads Series from DB.

And I should calculate and aggregate the Mean and Confidence Value (two components of Value At Risk)

Plamen Alexiev

[marcus_sommer]

It looked that you need to include your other dimensions from the pivot into the dimensions-list from the aggr(), too and also a NODISTINCT might be necessary. Further it might be that you need a TOTAL statement within the outer aggregation.

- Marcus

[plamen_alexiev]

Hello Mr. Sommer,

Thank you for tots suggestions. You are probably right - the matter is in qualifiers...., but which combination of qualifiers?

I was trying many combinations of modifiers Total, Distinct, NoDistinct, Only() on my nested aggregations, but without success.

Moreover, in one case I was supplied on every level of aggregation list of current positions (COMPONENTID is position number) :

=Min(All Aggr(NoDistinct Sum ({$<COMPONENTID={$(=Concat(Chr(39) & COMPONENTID & Chr(39),','))}>} VALUE),RUNNUMBER),$(vIdx))

This expression, also returns wrong results on sub-levels, but returns correct result on top aggregation level!

OK, soon I will send you my qvw file...

Thank you for your attention.

Plamen Alexiev

[marcus_sommer]

It's difficult to say what might be wrong but I would rather try to replace the ALL statement which is outdated with a Set Analysis like:

=Min({1} TOTAL Aggr(NoDistinct Sum ({$<COMPONENTID={"$(=Concat(Chr(39) & COMPONENTID & Chr(39),','))"}>} VALUE),RUNNUMBER),$(vIdx))

Although the NODISTINCT is an option for an aggr() there are rather not many use-cases for it and therefore I would make the most testings without it.

Another point you mentioned is that the calculation is right for the totals but not on row-level. This hints to the fact that:

$(=Concat(Chr(39) & COMPONENTID & Chr(39),','))

is an adhoc-variable which could have only one value at the same time and what meant that is calculated globally before the chart iself is calculated and applied there for each row. It's not really clear for me what the purpose of this concat() is but I suggest to consider if it couldn't be replaced with anything else - maybe a flag within the script.

- Marcus