Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

variables in correl using set analysis

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. 

CORRCORR

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?


1 Solution

Accepted Solutions
Highlighted
Contributor II
Contributor II

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])))

View solution in original post

3 Replies
Highlighted
Creator III
Creator III

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]))

Highlighted
Contributor II
Contributor II

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


Highlighted
Contributor II
Contributor II

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])))

View solution in original post