topic Re: Problem with correl expression in QlikView

Problem with correl expression

curiousfellow — Thu, 30 Apr 2015 08:55:02 GMT

Why is this expression wrong ?

Correl(({$<

[Year]={$(HighestYear)},

MainQuestion ={">=$(=0)"}

>} MainQuestion)

({$<

[Year]={$(HighestYear)},

[question]={'Otherquestion'},

score={">=$(=0)"}

>} score))

When I seperate the expression in the part before the comma and after the comma and use it with avg both expressions are correct :

avg ({$<

[Year]={$(HighestYear)},

MainQuestion ={">=$(=0)"}

>} MainQuestion)

and

avg ({$<

[Year]={$(HighestYear)},

[question]={'Otherquestion'},

score={">=$(=0)"}

>} score)

Your help will be appreciated

Re: Problem with correl expression

Gysbert_Wassenaar — Fri, 01 May 2015 07:59:00 GMT

{$<

[Year]={$(HighestYear)},

MainQuestion ={">=$(=0)"}

>} MainQuestion

This is not an expression. You probably have to add an aggregation function and use the aggr function. Perhaps you need something like this:

Correl(aggr(only({$<

[Year]={$(HighestYear)},

MainQuestion ={">=$(=0)"}

>} MainQuestion), MyDimension1, MyDimension2, ... , MyDimensionX)

aggr(only({$<

[Year]={$(HighestYear)},

[question]={'Otherquestion'},

score={">=$(=0)"}

>} score), MyDimension1, MyDimension2, ... , MyDimensionX))

Replace MyDimension1, MyDimension2, ... , MyDimensionX with the dimensions over which you want to aggregate.

Re: Problem with correl expression

curiousfellow — Fri, 01 May 2015 08:31:06 GMT

I am really new to Qlikview, and was just studying the aggr function.

I know that it is not an expression, in my expression it is the first part of the correl expression.

Could you please explain why you use so much dimensions in the first part of the correl expression.

When I simplify the correl expression it looks like

Correl(question1,question2)

However I want a value for

Correl(question1,question2) and

Correl(question1,question3) and

Correl(question1,question4) etc

I want the correl values to be presented in a scatter chart, the average of every question has to be the dimension, the correl is the expression.

Thanks for your answer, I certainly will try your expresion

Re: Problem with correl expression

curiousfellow — Fri, 01 May 2015 11:49:33 GMT

I found a solution for this particular problem, looks like this :

Correl(if(Year=Highestyear,if(Mainquestion > 0,Mainquestion)),if(year=Highestyear,if(Mainquestion > 0,if(score > 0,score))))

Probably not the best solution, but works for now