topic Dynamically finding correlations in QlikView

Dynamically finding correlations

Anonymous — Mon, 14 Nov 2016 18:44:00 GMT

Hello all,

I'm trying to figure out a way to dynamically show correlations between metrics. The metric names are grouped together in 1 field with another field showing the values. I'm changed this table in the script so that each metric is now it's own field associated with a value. I've kept the original table as well.

I'm trying to use the Correl function to show correlations between each metrics, however, there are nearly 80 metrics so making a correl function for each combination isn't really feasible. (ex: Correl(Metric1,Metric2), Correl(Metric1,Metric3), Correl(Metric2,Metric3), etc.)

Is there a way to make it dynamic to show correlation percentages for a selected metric? I.E. if Metric1 is selected, the chart would show all correlation values for Metric1)

Here's an example of the tables:

My original table looks like this:

Account	Metric	Value
Account1	Metric1	832
Account1	Metric2	1032
Account1	Metric3	213
Account2	Metric1	3213
Account2	Metric2	3432
Account2	Metric3	7689
Account3	Metric1	1034
Account3	Metric2	1340
Account3	Metric3	8903

I took this table and pivoted it in the script so that I have a 2nd table like this:

Account	Metric1	Metric2	Metric3
Account1	832	1032	213
Account2	3213	3432	7689
Account3	1034	1340	8903

Re: Dynamically finding correlations

sunny_talwar — Mon, 14 Nov 2016 18:57:14 GMT

Would you be able to share what you have? In like a qvw file and may be we can take it forward? and also share some information about the expected output?

Re: Dynamically finding correlations

Anonymous — Mon, 14 Nov 2016 19:36:37 GMT

Thank you for the reply Sunny.

I'm working on getting a sample QVW up. The expected output would be to have the metrics as the dimension (all metrics except the one selected) with the correlation percentage as the expression.

So for instance, if Metric1 is selected, the output would be something like this:

Metric	Correlation
Metric2	80%
Metric3	50%
Metric4	3%
Metric5	68%

Edit:

Or if Metric2 is selected:

Metric	Correlation
Metric1	80%
Metric3	41%
Metric4	23%
Metric5	69%

Re: Dynamically finding correlations

Anonymous — Mon, 14 Nov 2016 20:01:56 GMT

I've attached a sample QVW

Re: Dynamically finding correlations

sunny_talwar — Mon, 14 Nov 2016 20:11:49 GMT

This is what you need?

No selection

Re: Dynamically finding correlations

Anonymous — Mon, 14 Nov 2016 20:22:05 GMT

I feel like it's very close.

When you select a metric though I would not want to show that same metric in the dimension. For instance when you select Metric1, Metric1 would not appear on the chart since it will always have a correlation with itself if that makes sense.

I also don't want to use conditional shows for expressions since I will have so many combinations, it will be incredibly cumbersome.

Re: Dynamically finding correlations

sunny_talwar — Mon, 14 Nov 2016 20:24:36 GMT

Try this:

Correl({<_Metric = e(Metric)>}Value, _Value)

Re: Dynamically finding correlations

sunny_talwar — Mon, 14 Nov 2016 20:26:02 GMT

Actually try this:

If(GetSelectedCount(Metric) > 0, Correl({<_Metric = e(Metric)>}Value, _Value), Correl(Value, _Value))

Re: Dynamically finding correlations

Anonymous — Mon, 14 Nov 2016 20:54:42 GMT

That works! Thank you so much Sunny!! You saved me a lot of script load time as well by eliminating the reverse-crosstable!