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

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

petr_bednarik — Sun, 14 Aug 2016 08:41:51 GMT

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

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

Anonymous — Sun, 14 Aug 2016 13:17:02 GMT

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

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

swuehl — Sun, 14 Aug 2016 13:31:02 GMT

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)

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

petr_bednarik — Sun, 14 Aug 2016 16:16:49 GMT

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