Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Listing top 10 and comparison by gender

Dear all

I am working on a movie dataset and is struggling to display the following information on my dashboard.

My database has the following columns: MovieTitle, Gender, Ratings

I need to display the following:

List the top 10 movies that, on average, men rate higher than women.


I assume that we have to use a List Box for the above.

I created a List Box with MovieTitle as the Dimension.

I am not sure what to add to the expression such that it filters out only the top 10 movies (based on Average Ratings calculated) that men rated higher than women.


Could someone please help to provide some clear advise.


Thank you so much!

24 Replies
Not applicable
Author

Thanks Sunny and Andy

The top 10 will be based on the following.

There will be movies where males ranked it higher than females.

Out of all the movies where in total, men ranked it higher than women, list the top 10 of those movies (based on the difference in the rank score btw men and women). I hope this helps!

Not applicable
Author

The raw data was taken from here:

https://grouplens.org/datasets/movielens/#attachments

Users, Movies and Ratings

Not applicable
Author

The raw data was taken from here:

https://grouplens.org/datasets/movielens/#attachments

Users, Movies and Ratings

passionate
Specialist
Specialist

Hi,

You can try:

Expression:

if(only({<Gender={'male'}>}Rate)>only({<Gender={'female'}>}Rate),only({<Gender={'male'}>}Rate),null())

Dimension:

Moviename

After this apply dimension limit for top 10.

Regards,

Pankaj

Not applicable
Author

Attaching one of the scaled down ratings file.

MovieID represents my MovieTitle.

There is a separate excel sheet for user and gender information.

Not applicable
Author

Hi Pankaj

Can you explain the code please?

Thank you

passionate
Specialist
Specialist

Hi Jess,

if(only({<Gender={'male'}>}Rate)>only({<Gender={'female'}>}Rate),only({<Gender={'male'}>}Rate),null())

1. only({<Gender={'male'}>}Rate):  this gives rating by Male

2. only({<Gender={'female'}>}Rate): this gives rating by female

3. So i have compared when rating by male is greater than female then take rating of male else null.


Now you can use dimension limit functionality to show top 10.


Regards,

Pankaj


Not applicable
Author

Hi Pankaj, I'm sorry but I can't open other files using the personal edition.

I generated the table below. Can I confirm the Rating is the difference between the total male and female average ratings for that particular movie?

Table3.jpg

passionate
Specialist
Specialist

Hi Jess,

Sorry my mistake.

I thought there there is aggregated male female rating.
You will need to change the expression to:

if(Avg({<Gender={'male'}>}Rate)>Avg({<Gender={'female'}>}Rate),Avg({<Gender={'male'}>}Rate),null())


Replace only by avg.


Regards,

Pankaj

Not applicable
Author

Yup, I added the average to the calculation. Thanks

But how do I get the table to display the difference in the avg ratings instead of just the average rating by the men?

Meaning (total men average rating - total female average rating) for each movie title listed.