I need to be able to have a paired bar chart where 1 bar can be filtered by year BUT is always the average for all responses. The other bar can be filtered by year and is specific company response. Currently my expressions are;
Group Average: = avg ( distinct {1<QPermID={"AM0425_R*"}>} Response)
Company Response: = MAX( distinct {$<QPermID={"AM0425_R*"}>} Response)
If I want to change the year, the Company Response responds as expected. I can filter by a specific company and a specific year. However, with a set expression of '1', my Group Response doesn't change.
In other expression in my app, I use the orange expression below. This allows the Group response to always be the max year and it will also change to whatever is the max year that I've selected in my filter...
I have tried placing {1<Year = {$(=Max(num#(Year ,'####')))}>} into my group and company responses for the paired bar, but they are ignored. The expression isn't broken, it just doesn't get applied. That means my Group Average is always inclusive of all years of data.
In my example App,
Sheet 1 "Example Bar" shows the data. It's already set to max year. The average for that data is 46.4.
Sheet 2 "Paired Bar" shows my objective graph. The average for the far left bar should also be 46.4. However, because it doesn't respond to filter nor my Max Year expression ({1<Year = {$(=Max(num#(Year ,'####')))}>}) it's using all year's data and instead results in an average of 52.8.
I'm open to a whole new expression as long as it gets me what I need. Thanks
