topic Re: Correlation Analysis on two columns of a Pivot Chart in QlikView

Correlation Analysis on two columns of a Pivot Chart

Wed, 25 Nov 2020 16:16:04 GMT

I need help in figuring out a way to do a correlation analysis on two columns of a Pivot Chart.

I have following two dimensions in my dataset

1. Trade Date

2. Exchange ( this has values like NYSE, NASDAQ, CME, etc)

and the Measure is

1. Trade Value ( numeric)

Question -

I created a Pivot chart on the above mention dataset. It looks something like below.

Is there an easy way to perform a correlation analysis on two columns of a Pivot Chart???

I want to perform Correl(NYSE, NASDAQ). These two columns are field values of the Dimension - Exchange.

Trade Date	NYSE	NASDAQ
1/1/2014	3	2
1/2/2014	4	16
1/3/2014	5	15
1/4/2014	12	6
1/5/2014	9	14

Re: Correlation Analysis on two columns of a Pivot Chart

kaushiknsolanki — Wed, 08 Feb 2017 16:36:02 GMT

Hi,

Have a look at the document given on below link. That will help.

Understanding Correlation Analysis and how to apply to Qlikview.docx

Regards,

Kaushik Solanki

Re: Correlation Analysis on two columns of a Pivot Chart

awhitfield — Wed, 08 Feb 2017 16:36:56 GMT

Hi,

check out this link:

https://community.qlik.com/docs/DOC-7855

Andy

Re: Correlation Analysis on two columns of a Pivot Chart

awhitfield — Wed, 08 Feb 2017 16:37:21 GMT

You just beat me to it!

Re: Correlation Analysis on two columns of a Pivot Chart

sunny_talwar — Wed, 08 Feb 2017 16:39:25 GMT

This?

Re: Correlation Analysis on two columns of a Pivot Chart

kaushiknsolanki — Wed, 08 Feb 2017 16:39:26 GMT

Haha,

Doesnt matter who beats whom. What matters is that Vaibhav gets the help he is seeking for.

Regards,

Kaushik Solanki

Re: Correlation Analysis on two columns of a Pivot Chart

Wed, 08 Feb 2017 16:41:19 GMT

This works only if you have two measures.

My issue is that I need correlation on same measure but on difference values of the dimension, aggregated by Trade date.

Re: Correlation Analysis on two columns of a Pivot Chart

kaushiknsolanki — Wed, 08 Feb 2017 16:43:59 GMT

Then you should use the Generic load in script to create a separate field for each value of your dimension.

Help in Generic load.

https://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

Sample Generic Load Script

Regards,

Kaushik Solanki

Re: Correlation Analysis on two columns of a Pivot Chart

Wed, 08 Feb 2017 16:44:24 GMT

I have 100 Million + rows

Not sure if my laptop can handle two similar version of such a huge data at the same time.

Re: Correlation Analysis on two columns of a Pivot Chart

sunny_talwar — Wed, 08 Feb 2017 16:46:47 GMT

Perform the Correl in the script? Does it have to change based on selections? I don't see another way to do correlation unless I have not been exposed to it .

Also, is the output what you want or no?

Re: Correlation Analysis on two columns of a Pivot Chart

Wed, 08 Feb 2017 16:51:57 GMT

Yes.. Exaclty I want to perform Correl dynamically, based on various other selections.

Do you know of if it is possible to refer to entire column of Pivot Chart and use it in a function.

What I am thinking is - Lets say there was a way where I could refer to the second and Third column of Pivot Chart i.e and NYSE and NASDAQ and directly use it in Correl. Function

Correl ( PivotChart Column2, PivotChart Column3)

Re: Correlation Analysis on two columns of a Pivot Chart

sunny_talwar — Wed, 08 Feb 2017 16:54:28 GMT

I don't think so... but you can look into Kaushik's suggestion of Generic load....

Re: Correlation Analysis on two columns of a Pivot Chart

kaushiknsolanki — Wed, 08 Feb 2017 16:56:20 GMT

Vaibhav,

The way the correl() function is created inbuilt, is it requires 2 fields which are coming from database.

So i think only way is generic load in your case. Try with a small data and see how it goes and then you can decide about using it with huge data set.

Regards,

Kaushik Solanki

Re: Correlation Analysis on two columns of a Pivot Chart

jayanttibhe — Wed, 08 Feb 2017 17:07:04 GMT

Like Attached ?

Correl() returns the aggregated correlation coefficient for two data sets. The correlation function is a measure of the relationship between the data sets and is aggregated for (x,y) value pairs iterated over the chart dimensions.