topic Re: Correlation in Chart in QlikView

Correlation in Chart

Sun, 06 Jan 2013 11:29:23 GMT

Hi,

I have a chart with 2 expressions over one dimesion,

i'm trying to show somewhere in the chart the correlation between the 2 expressions-

in the exmple below- what is the correlation between MU Delta and $GP Delta.

is this possible?

Thanks!!

Re: Correlation in Chart

nagaiank — Sun, 06 Jan 2013 12:44:00 GMT

You may add a text box to display the value =correl([MU Data], [$GP Delta])

Extract from the Qlikview Reference Manual for Correl function is:

correl ([{set_expression}] [ distinct ] [ total [<fld {,fld}>] ] xexpression,

y-expression )

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

and y-expression iterated over the chart dimension(s). 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. See also the rangecorrel(

x-value , y-value { , x-value , y-value}) function.

X-expression and y-expression must not contain aggregation functions, unless these inner aggregations contain

the total qualifier. For more advanced nested aggregations, please use the Advanced Aggregation function in combination with calculated dimensions, see Add calculated dimension... .

This function has the same limitations for nested aggregation as the avg([{set_expression}] [ distinct ] [

total [<fld { , fld } >]] expression) function. The correl function supports Set Analysis and the total qualifier in the same way as the avg([{set_expression}] [ distinct ] [ total [<fld { , fld }

>]] expression) function.

Examples:

correl( Payments, Dates )

correl( A/B, X*Y/3 )

correl( total Payments, Dates )

correl( total <Grp> Payments, Dates)

Hope this helps.

Re: Correlation in Chart

Sun, 06 Jan 2013 13:31:13 GMT

but the 'MU Delta' and '$GP Delta' are expressions in the chart- not fields,

i tried to add in the chart properties: Presentation>Text in Chart: =correl([MU Delta],[$GP Delta])

but it doesnt seem to work.

am I doing somehing wrong?

Re: Correlation in Chart

nagaiank — Sun, 06 Jan 2013 13:53:29 GMT

Replace [MU Data] and [$GP Delta] with the corresponding expressions in the chart text dialog box by clicking on the ... button.

Re: Correlation in Chart

Tue, 08 Jan 2013 10:02:14 GMT

of course, i tried that,

the problem is that my expressions include aggregation functions (sum,count),

and i cant find a way to make it work,

also tried with variables but it didnt seem to work...

Re: Correlation in Chart

Mon, 24 Jun 2013 07:41:52 GMT

A similar experiment is working for me. Try testing the same in a text box first. Text in the box = correl (X-expression, Y-expression). You could include your aggregations, sums, averages, what have yous in these two expressions. Check if the text box displays it correctly. You could then fetch the data from the text box into the chart.

However, given that you seem to want it in the chart itself, and for future use (maybe future use is all that's to hope for, given how belated this reply is):

The linear trendline option does the same in the expressions tab of any chart. Isn't that a direct tool to have exactly what you seek, or I am being too naive in interpreting your situation?

Re: Correlation in Chart

Tue, 04 Nov 2014 08:08:28 GMT

I think Shiran would like to correlate 2 different selections, but the correlation formula only allows 1 set analysis, you cannot have different set analysis for y-axis and x-axis.

I am having the same problem on how to correlate the 2 expressions.