
Re: How to calculate covariance between two variables in qlikview? Possible???
Anand Chouhan Oct 20, 2014 9:18 AM (in response to Janani Kumar)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???
Janani Kumar Oct 20, 2014 9:52 AM (in response to Anand Chouhan)I understand...@How do you calculate Covariance of X and Y in qlikview


Re: How to calculate covariance between two variables in qlikview? Possible???
Clever Anjos Oct 20, 2014 9:30 AM (in response to Janani Kumar)QlikView has a lot of statistical functions, please check your manual
Statistical Aggregation Functions in Script
correl(xexpression, yexpression)
Returns the aggregated correlation coefficient for a series of coordinates represented by paired numbers in xexpression
and yexpression 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 datapair 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 (yexpression, xexpression [, 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 xexpression and yexpression 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 datapair to be disregarded.
An optional value y0 may be stated forcing the regression line to pass through the yaxis 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 datapairs 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 (yexpression, xexpression [, y0 [, x0 ]])
returns the aggregated b value (yintercept) of a linear regression defined by the equation y=mx+b for a series
of coordinates represented by paired numbers in xexpression and yexpression 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 datapair to be disregarded.
An optional valye y0 may be stated forcing the regression line to pass through the yaxis 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 datapairs 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 (yexpression, xexpression [, 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 xexpression and yexpression 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 datapair to be disregarded.
An optional valye y0 may be stated forcing the regression line to pass through the yaxis 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 datapairs 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 (yexpression, xexpression [, 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 xexpression and yexpression 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 datapair to be disregarded.
An optional valye y0 may be stated forcing the regression line to pass through the yaxis 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 datapairs 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 (yexpression, xexpression [, y0 [, x0 ]])

Re: How to calculate covariance between two variables in qlikview? Possible???
Janani Kumar Oct 20, 2014 9:53 AM (in response to Clever Anjos )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???
Jonathan Dienst Oct 20, 2014 10:21 AM (in response to Janani Kumar)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

Test2.qvw 138.2 K

Re: How to calculate covariance between two variables in qlikview? Possible???
Janani Kumar Jan 12, 2015 5:06 AM (in response to Jonathan Dienst )
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???
Clever Anjos Oct 20, 2014 3:59 PM (in response to Janani Kumar)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

138813.qvw 185.2 K

Re: How to calculate covariance between two variables in qlikview? Possible???
Janani Kumar Jan 12, 2015 5:08 AM (in response to Clever Anjos )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???
divya alav Apr 23, 2015 11:06 AM (in response to Janani Kumar)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