Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
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])))
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]))
Thanks for looking into this but it seems not working either.
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])))