Max Year as Set Expression

[wttaryde]

Hi,

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...

=(Avg({1<Year = {$(=Max(num#(Year ,'####')))}>} Aggr(Sum({1<QPermID={"AM0425_R_1*"}>}Response),YearID,Year)))

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

[Kushal_Chawda]

Can you create normal bar graph and send the QVF because I don't have vizlib extensions

[wttaryde]

Here's the App without vizlib extentsions.

[Kushal_Chawda]

I can still see vizlib

[wttaryde]

Sorry...

[Kushal_Chawda]

I get 47.6  How do you get 46.4?

[wttaryde]

Hey, if you get 47.6, I take that calculation and run with it.

Sheet 1 - bar chart - has the data used for the first bar on sheet 2.  When filtered for 2019YE the first bar on sheet 2 should be 46.4.  i only ever see 52.8

[Kushal_Chawda]

try below expression in your bar

=sum( {<QPermID={"AM0425_R*"}>} Response)/Count( {<QPermID={"AM0425_R*"}>} CompID)

[Kushal_Chawda]

if you just want the avg of Response >o then use below

=sum( {<QPermID={"AM0425_R*"},Response={">0"}>} Response)/Count( {<QPermID={"AM0425_R*"},Response={">0"}>} CompID)

[wttaryde]

Thank, I appreciate the effort.  I can calculate the average.  What I need to do is somehow get the average to only be for the max year in my data set without having a filter applied. If my expression allows for a filter by year, it also allows for a filter by company.  I don't want to do that. 

This expression calculates the correct average for the max year

Avg ({1<Year = {$(=Max(num#(Year ,'####',)))}>}Total Aggr(
if(count ({1<QPermID={"AM0425_R*"}>}Response)>0,max({1<QPermID={"AM0425_R*"}>}Response),Null())
,YearID,Year))

However, when used in my paired bar chart all of my bars are the same because it's not taking each piece of the question AM0425.

My company bar uses the expression: 

= MAX( distinct {$<QPermID={"AM0425_R*"}>} Response)

every bar of the paired bar chart then reflects a different piece of the question AM0425. 

Somehow I need to modify my group expression to be distinct or my company expression to have the year = max year.