Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
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)
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