# 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.

Thank you so much!

1 Solution

Accepted Solutions
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
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.

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?

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.

 MovieID MovieGenres.Title GenreCategory UserID Rating Gender 1 Toy Story (1995) Animation 1 5 M 2 Jumanji (1995) Adventure 1 3 M 3 Grumpier Old Men (1995) Comedy 1 3 F 4 Waiting to Exhale (1995) Comedy 2 4 M 5 Father of the Bride Part II (1995) Comedy 34 5 M 6 Heat (1995) Action 1 3 M 7 Sabrina (1995) Romance 56 5 M 8 Tom and Huck (1995) Adventure 108 5 M 9 Sudden Death (1995) Action 2 4 M 10 GoldenEye (1995) Action 2 4 F 11 American PresidentThe (1995) Comedy 66 5 F 12 Dracula: Dead and Loving It (1995) Horror 66 4 M 13 Balto (1995) Animation 87 4 M 14 Nixon (1995) Drama 65 4 F 15 Cutthroat Island (1995) Action 56 5 M 16 Casino (1995) Drama 56 4 M 17 Sense and Sensibility (1995) Romance 200 3 M 18 Four Rooms (1995) Thriller 200 4 M 19 Ace Ventura: When Nature Calls (1995) Comedy 450 5 M 20 Money Train (1995) Action 71 5 F
Not applicable

## Re: Listing top 10 and comparison by gender

 MovieID Title GenreCategory UserID Rating Gender 1 Toy Story (1995) Animation 1 5 M 2 Jumanji (1995) Adventure 1 3 M 3 Grumpier Old Men (1995) Comedy 1 3 F 4 Waiting to Exhale (1995) Comedy 2 4 M 5 Father of the Bride Part II (1995) Comedy 34 5 M 6 Heat (1995) Action 1 3 M 7 Sabrina (1995) Romance 56 5 M 8 Tom and Huck (1995) Adventure 108 5 M 9 Sudden Death (1995) Action 2 4 M 10 GoldenEye (1995) Action 2 4 F 11 American PresidentThe (1995) Comedy 66 5 F 12 Dracula: Dead and Loving It (1995) Horror 66 4 M 13 Balto (1995) Animation 87 4 M 14 Nixon (1995) Drama 65 4 F 15 Cutthroat Island (1995) Action 56 5 M 16 Casino (1995) Drama 56 4 M 17 Sense and Sensibility (1995) Romance 200 3 M 18 Four Rooms (1995) Thriller 200 4 M 19 Ace Ventura: When Nature Calls (1995) Comedy 450 5 M 20 Money Train (1995) Action 71 5 F
Not applicable

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?

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

## 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?