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

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

    Janani Kumar

      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???
          Anand Chouhan

          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???
            Clever Anjos

            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???

              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