Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Max Year as Set Expression

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

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

try this

=sum( {<QPermID={"AM0425_R*"},Response={">0"},Year = {$(=Max(num#(Year ,'####',)))}>} Response)/
Count({<QPermID={"AM0425_R*"},Response={">0"},Year = {$(=Max(num#(Year ,'####',)))}>} CompID)

View solution in original post

13 Replies
Highlighted
MVP
MVP

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

Highlighted
Contributor III
Contributor III

Here's the App without vizlib extentsions.

Highlighted
MVP
MVP

I can still see vizlib

Highlighted
Contributor III
Contributor III

Sorry...

Highlighted
MVP
MVP

I get 47.6  How do you get 46.4?

Highlighted
Contributor III
Contributor III

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

Highlighted
MVP
MVP

try below expression in your bar

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

Highlighted
MVP
MVP

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)

Highlighted
Contributor III
Contributor III

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.