calculate correlation using variables Qlik Sense

[Death4Free]

Hi!

I have two variables defined in Qlik Sense variables editor:

Name	Definition
vVar1	sum(Field1)
vVar2	sum(Field2)/sum(Field3)

I need to create table like this:

Dimension	Correlation
Item 1	0.9
Item 2	0.6

How i can calculate correlation?

For now i am trying in expression editor :

RangeCorrel($(vVar1),$(vVar2)), but result is "-" for every item.

What is wrong?

[petter]

RangeCorrel() is a range function and as such will only accept a list of discrete values arranged as pairs or a list of values returned from the inter-record functions Above, Below, Before, After, Top, Bottom, First and Last.

It might be that the Correl() function is the one you should use since it takes bare fields as parameters - not including any aggregation function.

You might need to use the Aggr() function along with the Correl() if you need to use Sum() to sum up before you caluculate the correlation.

[Death4Free]

Please, can you give me example of formula how it could looks with Aggr() and Correl()?

[petter · Accepted solution]

Correl( Aggr( Sum(Field1) , Dimension) , Aggr( Sum(Field2)/Sum(Field3) , Dimension ) )

Aggr( <aggreation-expression> , Dimension) will return a number of results from the aggregation-expression grouped by Dimension. So if Dimension contains 4 distinct values then you will get the four return values. These will be returned to Correl() function as the set of values.

[Death4Free]

I will give you more details: In data table i have customers, items, DateMY, salesSum, salesQuantity, salesProfit (customers, items, DateMY not unique values).

So what will be formula for table with first column as Customers and second column Correlation?

Is it correct

Correl( Aggr( Sum(salesQuantity) , DateMY, items) , Aggr( Sum(salesSum)/Sum(salesProfit) , DateMY, items) ) ?

[raman_rastogi]

Correl( Aggr( Sum(salesQuantity) , DateMY, items) , Aggr( Sum(salesSum)/Sum(salesProfit) , DateMY, items) )

It Seems Ok did you try this  ?

Or

try below  expression in your chart with dimension

Correl(SalesWQuantity,salesSum/SalesProfit)

There is some Limitation with this function

The expression must not contain aggregation functions, unless these inner aggregations contain the TOTAL qualifier. For more advanced nested aggregations, use the advanced aggregation function Aggr, in combination with calculated dimensions.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/Statistic...

Regards

Raman

[Death4Free]

Thanks.