variables in correl using set analysis

[chiayaochang]

Hi,

I have a table to carry time series data with 3 categories and hits. I am trying to run a correlation between 2 out of 3 selections in a KPI. 

CORR

I set 2 variables:
variable1 = SubField(Concat(GetFieldSelections([Sheet1-2.category]),'","'),',',1)
variable2 = SubField(Concat(GetFieldSelections([Sheet1-2.category]),'","'),',',2)

and filter pane with [Sheet1-2.category]

when I select milk and coffee in the filter pane 

The KPI returns nothing in my formula

=correl( aggr(only({<[Sheet1-2.category]={'(=$(variable1))'}>}[Sheet1-2.Hits]),[Sheet1-2.Hits]), aggr(only({<[Sheet1-2.category]={'(=$(variable2))'}>}[Sheet1-2.Hits]),[Sheet1-2.Hits]))

Is there anything wrong with the formula, please?

[Qlik1_User1]

Try this

=correl( aggr(only({<[Sheet1-2.category]={'($(=variable1))'}>}[Sheet1-2.Hits]),[Sheet1-2.Hits]), aggr(only({<[Sheet1-2.category]={'($(=variable2))'}>}[Sheet1-2.Hits]),[Sheet1-2.Hits]))

[chiayaochang]

Thanks for looking into this but it seems not working either.

[chiayaochang · Accepted solution]

Since I limit to 2 selections, I work it around by following formula. 

if(GetSelectedCount([Sheet1-2.category])<>2,'Please select two codes', correl(aggr(sum({<[Sheet1-2.category]={'$(=minstring([Sheet1-2.category]))'}>}[Sheet1-2.Hits]),[Sheet1-2.Hits]),aggr(sum({<[Sheet1-2.category]={'$(=maxstring([Sheet1-2.category]))'}>}[Sheet1-2.Hits]),[Sheet1-2.Hits])))