Qlik Community

Qlik Sense App Development

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

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

Tags (2)
3 Replies

Re: Filter bar Chart on Value Ranges

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


talk is cheap, supply exceeds demand
Not applicable

Re: Filter bar Chart on Value Ranges

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.

Re: Filter bar Chart on Value Ranges

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