Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
I'm open to a whole new expression as long as it gets me what I need. Thanks
try this
=sum( {<QPermID={"AM0425_R*"},Response={">0"},Year = {$(=Max(num#(Year ,'####',)))}>} Response)/
Count({<QPermID={"AM0425_R*"},Response={">0"},Year = {$(=Max(num#(Year ,'####',)))}>} CompID)
Can you create normal bar graph and send the QVF because I don't have vizlib extensions
Here's the App without vizlib extentsions.
I can still see vizlib
Sorry...
I get 47.6 How do you get 46.4?
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
try below expression in your bar
=sum( {<QPermID={"AM0425_R*"}>} Response)/Count( {<QPermID={"AM0425_R*"}>} CompID)
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)
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.