topic Re: Filter bar Chart on Value Ranges in App Development

Filter bar Chart on Value Ranges

Sat, 16 Nov 2024 10:43:56 GMT

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

Re: Filter bar Chart on Value Ranges

Gysbert_Wassenaar — Sun, 20 Mar 2016 12:51:35 GMT

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

Re: Filter bar Chart on Value Ranges

Sun, 20 Mar 2016 16:38:58 GMT

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

Gysbert_Wassenaar — Sun, 20 Mar 2016 17:17:22 GMT

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.