topic Value at Risk Calculations in QlikView

Value at Risk Calculations

Thu, 12 Aug 2010 09:15:56 GMT

Hi Folks

Quick Question:

Has anybody out there created a QlikView app that calculates Value at Risk?

I have charts that are doing this but the performance is really bad due to the aggregation taking place and then having to take the nth value from the resulting data set. I am wondering if there is a better, more efficient way to do this.

Here is a copy of the formula currently in use:

-min(
 aggr(
 sum(VaRPnL)
 ,VaRPnLDate
 ,CobDate
 ,Region
 ,Division
 ,Business
 ,[Sub Business]
 ,[Desk Location]
 ,Desk
 ,[Sub Desk]
 ,Book
 )
 ,( ( ( 1 - vVaR% ) * 500 + 1 ) + 1 )
)

Thanks for any help,

Value at Risk Calculations

jbb — Wed, 08 Sep 2010 07:46:56 GMT

Hi Nigel,

I'm working on the same calculation and have run into the same issue with calculation performance. If you managed to find anything that improved the performance, it would be very useful to share the results.

Equally, if I work something out, I'll post my results here.

Kind regards,

James.

Value at Risk Calculations

jbb — Wed, 06 Oct 2010 04:22:16 GMT

As an update to this, we found that using the Fractile function to return the nth value performed significantly better than using Min.

In your case, the formula would become:

fractile(aggr(sum(VaRPnL), VaRPnLDate ,CobDate ,Region ,Division ,Business ,[Sub Business] ,[Desk Location] ,Desk ,[Sub Desk] ,Book), 0.998-(vVAR%/100))

What we also discovered was that it may be acceptable to the business to simply show them the complete 500 scenario sorted list at each level of the hierarchy, using conditional formatting to highlight the relevant scenario based on the entered VAR%.

Using this method then makes it more simple to calculate values for MVaR, IVaR, ETL and CLL.

Re: Value at Risk Calculations

Lee_Matthews — Mon, 29 Jul 2013 04:24:11 GMT

I have been trying to work this out myself. The formula that worked for me at the highest level (ie. not breaking down by any dimensions) was:

sum( {<SCENARIO={'$(=FirstSortedValue(SCENARIO, AGGR(sum(VAR_P&L), SCENARIO), $(=vPercentileLowMember)))'}>} VAR_P&L)

sum( {<SCENARIO={'$(=FirstSortedValue(SCENARIO, AGGR(sum(VAR_P&L), SCENARIO), $(=vPercentileHighMember)))'}>} VAR_P&L) -

sum( {<SCENARIO={'$(=FirstSortedValue(SCENARIO, AGGR(sum(VAR_P&L), SCENARIO), $(=vPercentileLowMember)))'}>} VAR_P&L)

)*(vPercentileFraction/1)

Where this expression relies upon the following variables:

vPercentileMember=(max(SCENARIO_COUNT+1)*(1-vPercentile))
vPercentileLowMember=FLOOR((max(SCENARIO_COUNT+1)*(1-vPercentile)))
vPercentileHighMember=CEIL((max(SCENARIO_COUNT+1)*(1-vPercentile)))
vPercentileFraction=vPercentileLowMember-vPercentileMember

Re: Value at Risk Calculations

Fri, 23 May 2014 16:28:13 GMT

Are you guys using the Monte Carlo method?

Thanks,

Rodrigo

Re: Value at Risk Calculations

plamen_alexiev — Thu, 20 Sep 2018 08:36:29 GMT

Hi Rodrigo,

Do you solve a problem? I am also trying to calculate and aggregate Monte Carlo VaR in likewise manner, but got correct results only on top level

Pl.Alexiev

20.09.1018