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

[petr_bednarik]

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

[Anonymous]

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 · Accepted solution]

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]

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