
Re: calculate correlation using variables Qlik Sense
Petter Skjolden Nov 6, 2018 3:43 AM (in response to Aleksandrs Ivanovs)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 interrecord 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.

Re: calculate correlation using variables Qlik Sense
Aleksandrs Ivanovs Nov 6, 2018 4:13 AM (in response to Petter Skjolden )Please, can you give me example of formula how it could looks with Aggr() and Correl()?

Re: calculate correlation using variables Qlik Sense
Petter Skjolden Nov 6, 2018 4:22 AM (in response to Aleksandrs Ivanovs)Correl( Aggr( Sum(Field1) , Dimension) , Aggr( Sum(Field2)/Sum(Field3) , Dimension ) )
Aggr( <aggreationexpression> , Dimension) will return a number of results from the aggregationexpression 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.

Re: calculate correlation using variables Qlik Sense
Aleksandrs Ivanovs Nov 6, 2018 5:30 AM (in response to Petter Skjolden )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) ) ?

Re: calculate correlation using variables Qlik Sense
raman RASTOGI Nov 6, 2018 6:10 AM (in response to Aleksandrs Ivanovs)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.
Regards
Raman





Re: calculate correlation using variables Qlik Sense
Aleksandrs Ivanovs Nov 6, 2018 7:26 AM (in response to Aleksandrs Ivanovs)Thanks.