Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filter bar Chart on Value Ranges

Hello, I'm fairly new to qlik and I'm trying to do something that I think should be quite simple, but am having a bit of trouble.

I'd like to filter bar chart values, using a range filter.

The data I have is an average rate, the raw data has a range from 0 to about 1500.

I want the bar chart to count(averageRate) grouped by product where the average rate is less than say 800.

The SQL would look something like

SELECT

     Count(averageRate)

FROM

     TableA

WHERE

     AverageRate < 800

GROUP BY

     ProductID;

However I don't want to do this on the load script as I want to load all raw values, and have the user be able to select the range they are interested in looking at.

I've tried creating a "master dimension" that looks something like if([averageRate] < 800, [averageRate]), but then my filter shows all values that are below 800.

I thought one way of doing this would be to add another table in my DW something like DIM_Range that has an ID link to my fact table, but it seems like this would be a lot of work to do something that Qlik should be able to do on it's own.

Thanks for any help!

Jesse

3 Replies
Gysbert_Wassenaar

Try count({<averageRate={'<800'}>}averageRate) as expression with product as dimension.


talk is cheap, supply exceeds demand
Not applicable
Author

Hello,

Thank you for the suggestion, this would work if I wanted to hard code the bar chart to only show values less than 800, but I need the bar chart to be dynamic.

For example the 800 value in your expression needs to be a variable, that is populated by a filter selection. Is there some way to do this? It really seems like this should be a very simple and common function.

Thank you.

Gysbert_Wassenaar

Select only one averageRage value and try this: count({<averageRate={'<$(=only(averageRate))'}>}averageRate)

Or create a slider object and add two variables, e.g. vLowerLimit and vUpperLimit, and use this expression:

     count({<averageRate={'>=$(vLowerLimit)<=$(vUpperLimit)'}>}averageRate)


You will probably want to use something like =min({1}averageRate) and =max({1}averageRate) as the minimum and maximum values for the slider.


talk is cheap, supply exceeds demand