9 Replies Latest reply: Apr 23, 2015 11:06 AM by divya alav

How to calculate covariance between two variables in qlikview? Possible???

I'm working on statistical calculation of risk measures. I have the following Statictics meausre,

1. Standard deviation

2. Jensen's Alpha

3. Beta = Covariance(Stock return, Market return)/Variance(Market return)

4. Correlation

5. Sharpe Ratio

6. RSquared

I have found out that, in qlikview you have following Statistical function:

Standard dev = Stdev

Correlation = Correl(x,y)

Rsquared = linest_r2(y,x)

Now, How to calculate Beta and Sharpe Ratio in Qlikview????????? Please help!!!

Beta = Covariance(X,Y)/Variance(X)

Sharpe Ratio = Portfolio return - Rish free rate/ Standard Dev of portfolio returns

• Re: How to calculate covariance between two variables in qlikview? Possible???

Beta and Sharpe Ratio are formulas if so same you can create the variables for that.

• Re: How to calculate covariance between two variables in qlikview? Possible???

I understand...@How do you calculate Covariance of X and Y in qlikview

• Re: How to calculate covariance between two variables in qlikview? Possible???

QlikView has a lot of statistical functions, please check your manual

Statistical Aggregation Functions in Script

correl(x-expression, y-expression)

Returns the aggregated correlation coefficient for a series of coordinates represented by paired numbers in xexpression

and y-expression iterated over a number of records as defined by a group by clause. Text values,

null values and missing values in any or both pieces of a data-pair will result in the entire data-pair to be disregarded.

Example:

Load Month, correl(X,Y) as CC from abc.csv group by Month;

stdev([distinct] expression)

Returns the standard deviation of expression over a number of records as defined by a group by clause. If the

word distinct occurs before the expression, all duplicates will be disregarded.

304 QlikView 11.20 SR8

25 Script Expressions

Example:

Load Month, stdev(Sales) as SalesStandardDeviation from abc.csv group

by Month;

linest_m (y-expression, x-expression [, y0 [, x0 ]])

returns the aggregated m value (slope) of a linear regression defined by the equation y=mx+b for a series of

coordinates represented by paired numbers in x-expression and y-expression iterated over a number of records

as defined by a group by clause. Text values, null values and missing values in any or both pieces of a datapair

will result in the entire data-pair to be disregarded.

An optional value y0 may be stated forcing the regression line to pass through the y-axis at a given point.

By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate.

Unless both y0 and x0 are stated, the function requires at least two valid data-pairs to calculate. If y0 and x0

are stated, a single data pair will do.

Example:

Load Key, linest_m(Y,X) as Z from abc.csv group by Key;

linest_b (y-expression, x-expression [, y0 [, x0 ]])

returns the aggregated b value (y-intercept) of a linear regression defined by the equation y=mx+b for a series

of coordinates represented by paired numbers in x-expression and y-expression iterated over a number of records

as defined by a group by clause. Text values, null values and missing values in any or both pieces of a

data-pair will result in the entire data-pair to be disregarded.

An optional valye y0 may be stated forcing the regression line to pass through the y-axis at a given point.

By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate.

Unless both y0 and x0 are stated, the function requires at least two valid data-pairs to calculate. If y0 and x0

are stated, a single data pair will do.

Example:

Load Key, linest_b(Y,X) as Z from abc.csv group by Key;

linest_r2 (y-expression, x-expression [, y0 [, x0 ]])

returns the aggregated r2 value (coefficient of determination) of a linear regression defined by the equation

y=mx+b for a series of coordinates represented by paired numbers in x-expression and y-expression iterated

over a number of records as defined by a group by clause. Text values, null values and missing values in

any or both pieces of a data-pair will result in the entire data-pair to be disregarded.

An optional valye y0 may be stated forcing the regression line to pass through the y-axis at a given point.

By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate.

Unless both y0 and x0 are stated, the function requires at least two valid data-pairs to calculate. If y0 and x0

are stated, a single data pair will do.

Example:

Load Key, linest_r2(Y,X) as Z from abc.csv group by Key;

linest_sem (y-expression, x-expression [, y0 [, x0 ]])

returns the aggregated standard error of the m value of a linear regression defined by the equation y=mx+b

for a series of coordinates represented by paired numbers in x-expression and y-expression iterated over a

number of records as defined by a group by clause. Text values, null values and missing values in any or

both pieces of a data-pair will result in the entire data-pair to be disregarded.

An optional valye y0 may be stated forcing the regression line to pass through the y-axis at a given point.

By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate.

Unless both y0 and x0 are stated, the function requires at least two valid data-pairs to calculate. If y0 and x0

are stated, a single data pair will do.

Example:

Load Key, linest_sem(Y,X) as Z from abc.csv group by Key;

linest_seb (y-expression, x-expression [, y0 [, x0 ]])

• Re: How to calculate covariance between two variables in qlikview? Possible???

I have already mentioned that these are avaliable in qlikview and I have refered all those in Qlikview help. BUt how to calculate Covariance of x and y in qlikview

• Re: Re: How to calculate covariance between two variables in qlikview? Possible???

Hi

You would have to build these expressions as the built in stats functions do not include covariance (unless it can be derived from one of the correlation coefficient - my stats is rusty). Covariance would look something like:

=Sum(Aggr(([Stock return] - Avg(TOTAL [Stock return]))*([Market Return] - Avg(TOTAL [Market Return])), ID)) /

Count(ID)

Variance is the square of the std deviation, so the full beta calculation is

Sum(Aggr(([Stock return] - Avg(TOTAL [Stock return]))*([Market Return] - Avg(TOTAL [Market Return])), ID)) / Count(ID) / Pow(Stdev([Market Return]), 2)

These assume a key field called ID - so adjust according to your data model.

I think you need another piece of information for the Sharpe ratio - the risk free rate of return.

HTH

Jonathan

• Re: How to calculate covariance between two variables in qlikview? Possible???

Sorry for very late reply..I got busy on something and now again picked up this beta calculation. Your answer was so helpful to obtain the Beta results. I really appreciate your kind help

• Re: Re: How to calculate covariance between two variables in qlikview? Possible???

Sorry, I didn´t get you already found the manual

Covariance can be expressed as a function of correlation so

covar(X,Y) = Correl(X,Y) * Stdev(X) * Stdev(Y)

Please check attached an example

• Re: How to calculate covariance between two variables in qlikview? Possible???

Hi Clever Anjos, Thanks for your reply, just got to work on this. Your answer was helpful and I'm so thankful

• Re: How to calculate covariance between two variables in qlikview? Possible???

Hi,

I have a bar chart , and this is my expression in the chart

Expression : COUNT(if([SLA_DAYS_PAST]<=0,[PPL_ID]))/COUNT(if([SLA_FLAG]=-1,[PPL_ID]))  (sl_flag_past<=0 indicates compaints that are taken care of on or before due dates) and (sla_flag=-1 indicates all the closed complaints)

Calculated Dimension : if(CLOSE_DATE>=vMinDate, MonthName(CLOSE_DATE))

Now, first of all I need to build an SPC CHART, so I have calculated the average value using this expression:

Average: COUNT(TOTAL <PPL_ID> if([SLA_DAYS_PAST]<=0,[PLL_ID]))/COUNT(TOTAL<PPL_ID> if([SLA_FLAG]=-1,[PPL_ID]))

Now to calculate upper and lower control limits, i need to get the standard deviation:

uppercontrol_limit : average+STDEV

lower control_limit=average=STDEV

So, I need a formula to find the STDEV. I have a dead line, and i really appreciate your time.

-Sarayu