Qlik Community

Qlik Sense App Development

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

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
petr_bednarik
New 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
MVP
MVP

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

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)

3 Replies
galax_allu
Valued Contributor

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

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

MVP
MVP

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

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
New Contributor

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

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 doingSmiley Happy

Regards,

Petr

Community Browser