I want to create a gauge. I want the min and max to be set based on the metric for all companies. I then want to be able to filter by a company and have the min and max remain the same, but the value change to match the company.
I did this when I was using a simple expression and it worked. Selecting a company filters the value, but the min and max remain the same as before (based on all data).
When I try to use a complex expression, I can't get it right. Selecting a company filters the min and max, which isn't what I want.
Any ideas? (Also - side question - if using {$} in set analysis is the same as not stating any set expression, why bother putting it in at all?)
Simple Example that WORKS:
KPI: SUM({$<Question={"Cat1"}>}Response)
Minimum: =MIN({<Question={"Cat1"},Company>}Response)
Complex Example that DOESN'T WORK:
KPI:
(avg( {<Year = {"2019"}>} Aggr((
(Sum({$<Question={'Cat1'}>}Response)))
/(Sum({1<Question={'Cat4','Cat5','Cat6'}>}Response))
,CompName,Year)))
Minimum:
=(Min( {<Year = {"2019"}>} Aggr((
(Sum({$<Question={'Cat1'},Company>}Response)))
/(Sum({$<Question={'Cat4','Cat5','Cat6'},Company>}Response))
,CompName,Year)))
Thank you!
