Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try count({<averageRate={'<800'}>}averageRate) as expression with product as dimension.
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.
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.