Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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!

1 Solution

Accepted Solutions
passionate
Valued Contributor

Re: Listing top 10 and comparison by gender

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

24 Replies
henrikalmen
Contributor

Re: Listing top 10 and comparison by gender

Maybe you can achieve what you want by creating a table chart where you use the tab "Dimension Limits" (in chart properties) to show only the 10 largest values.

ogster1974
Honored Contributor II

Re: Listing top 10 and comparison by gender

A table with limitation seems the way to go.

How are you ranking your movies? By number of movie goers? Or by the male rating or by overall rating?

A calculated dimension something like

If(aggr(Avg({<gender={'male'}>} ratings),movietitle)>aggr(Avg({<gender={'female'}>} ratings),movietitle), movietitle,null())

Exclude nulls and set limitation fixed 10 on whatever you decide to measure your movies on should work.

Not applicable

Re: Listing top 10 and comparison by gender

Thank you all for the help.

Hi Andy

I created a Straight Table with Dimension MovieGenres.Title and inserted the following codes as a Calculated Dimension to that (suppress value when Null on).

N

=If(aggr(Avg({<Gender={'M'}>} Rating),MovieGenres.Title)>aggr(Avg({<Gender={'F'}>} Rating),MovieGenres.Title), MovieGenres.Title,null())

I get the output table below. Is a Straight Table the best option. I tried the code on a Pivot Table and got blanks. I tried a List Box as well and it didn't work.

Where did I go wrong?

result.jpg

A small sample of the data is provided below, combined data from 3 excel files.

Each User can rate more than one movie.

Each movie can be rated by many Users.

Each movie can have more than one genre

The MovieID and UserID is unique.

     

     

MovieIDMovieGenres.TitleGenreCategoryUserIDRatingGender
1Toy Story (1995)Animation15M
2Jumanji (1995)Adventure13M
3Grumpier Old Men (1995)Comedy13F
4Waiting to Exhale (1995)Comedy24M
5Father of the Bride Part II (1995)Comedy345M
6Heat (1995)Action13M
7Sabrina (1995)Romance565M
8Tom and Huck (1995)Adventure1085M
9Sudden Death (1995)Action24M
10GoldenEye (1995)Action24F
11American PresidentThe (1995)Comedy665F
12Dracula: Dead and Loving It (1995)Horror664M
13Balto (1995)Animation874M
14Nixon (1995)Drama654F
15Cutthroat Island (1995)Action565M
16Casino (1995)Drama564M
17Sense and Sensibility (1995)Romance2003M
18Four Rooms (1995)Thriller2004M
19Ace Ventura: When Nature Calls (1995)Comedy4505M
20Money Train (1995)Action715F
Not applicable

Re: Listing top 10 and comparison by gender

MovieIDTitleGenreCategoryUserIDRatingGender
1Toy Story (1995)Animation15M
2Jumanji (1995)Adventure13M
3Grumpier Old Men (1995)Comedy13F
4Waiting to Exhale (1995)Comedy24M
5Father of the Bride Part II (1995)Comedy345M
6Heat (1995)Action13M
7Sabrina (1995)Romance565M
8Tom and Huck (1995)Adventure1085M
9Sudden Death (1995)Action24M
10GoldenEye (1995)Action24F
11American PresidentThe (1995)Comedy665F
12Dracula: Dead and Loving It (1995)Horror664M
13Balto (1995)Animation874M
14Nixon (1995)Drama654F
15Cutthroat Island (1995)Action565M
16Casino (1995)Drama564M
17Sense and Sensibility (1995)Romance2003M
18Four Rooms (1995)Thriller2004M
19Ace Ventura: When Nature Calls (1995)Comedy4505M
20Money Train (1995)Action715F
Not applicable

Re: Listing top 10 and comparison by gender

Can anyone help please

MVP
MVP

Re: Listing top 10 and comparison by gender

Each Movie is only repeating once, how do you compare Men's rating vs Women's rating when only men or women rate them? Am I missing something?

ogster1974
Honored Contributor II

Re: Listing top 10 and comparison by gender

right so first things first.  Is the claculated dimension bringing out the movies where the average male rating is greater than the average female rating?

You can do this by adding the following measures to your straight table.

Average Female  Rating

Avg({<Gender={'M'}>} Rating)

Average Female  Rating

Avg({<Gender={'F'}>} Rating)

You should see only movies that have a Male rating > female

Next you said you wanted top 10 movies,  What is your Top 10 based on?

If you mockup up what you want it to look like in excel it might be easier to give you the guidence you seek. 

Not applicable

Re: Listing top 10 and comparison by gender

Hi Summy

The table is only a sample data. The listing goes on to thousands.

Each movie can be repeated as many times as each user rates it.

MVP
MVP

Re: Listing top 10 and comparison by gender

It might be helpful to see some raw data where you have repeating information for movie based on M or W and then let us know the output you are looking to get. Difficult to see how you determine top 10 here?