Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
petr_bednarik
Contributor
Contributor

How to filter based on expression values (i.e. measures)?

Hi,

I would like to be able to explore (meaning of do selections/filter) my data not only based on the data present in the original files but also based on expressions/measures which I create as part of the Qlik Sense App. Especially expressions involving aggregations such as counts and sums (example below). Is there any easy way to do that? I know I can pre-calculate the expressions/aggregations as part of the data load script but that is cumbersome and limits how people can explore the data up-front.


Example:

I have a file of user movie ratings (UserID, MovieID, Rating). I would like to look at only movies which have been reviewed by at least 5 people. I would like to be able to filter by MovieIDs where Count(Rating)>=5.

Thanks,

Petr

1 Solution

Accepted Solutions
swuehl
MVP
MVP

If I understood correctly, you can use an advanced search in you field modifier, for example if you are using a table chart with dimension MovieID and expression

=Avg(Rating)

, you can add a filter to this chart using a set expression like

=Avg( {<MovieID = {"=Count(Rating)>5"}>} Rating)

Only movies with more than 5 ratings will be shown.

You can also create a filter pane with a calculated field dimension to filter on the movie IDs, applying the filter to all charts:

=Aggr( If (Count({<MovieID=>} Rating)>5,'more than 5 ratings','less than 5 ratings'),MovieID)

View solution in original post

3 Replies
Anonymous
Not applicable

Hi

your query:

I would like to be able to filter by MovieIDs where Count(Rating)>=5.



try this  (set analysis)

Dimension: MovieID

expression : count  (  {$ <Rating={">=5"}  > }  MovieID)                           //for movieID  having rating greater than 5 rating

swuehl
MVP
MVP

If I understood correctly, you can use an advanced search in you field modifier, for example if you are using a table chart with dimension MovieID and expression

=Avg(Rating)

, you can add a filter to this chart using a set expression like

=Avg( {<MovieID = {"=Count(Rating)>5"}>} Rating)

Only movies with more than 5 ratings will be shown.

You can also create a filter pane with a calculated field dimension to filter on the movie IDs, applying the filter to all charts:

=Aggr( If (Count({<MovieID=>} Rating)>5,'more than 5 ratings','less than 5 ratings'),MovieID)

petr_bednarik
Contributor
Contributor
Author

Stefan,

thanks a lot. The Aggr thing is doing exactly what I wanted! Now I just need to get an understanding of what exactly it's doing:)

Regards,

Petr