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

Filtering out data

Dear all

I am working with the following data and is required to list the genres of movies that Lawyers liked the most (based on Rating).

I need to create a bar chart to list the Title and the Total Rating (only ratings by Lawyers and exclude all other occupation).

What codes do I place in my expression to calculate the total ratings of lawyers only?

Thank you!

     

MovieIDTitleRatingGenresUserIDGenderOccupation
1Toy Story (1995)5Animation719MLawyer
2Jumanji (1995)4Adventure1056MStudent
3Grumpier Old Men (1995)4Comedy1187FLawyer
4Waiting to Exhale (1995)5Comedy1200MLawyer
5Father of the Bride Part II (1995)5Comedy1296MLawyer
6Heat (1995)4Action1458MTeacher
7Sabrina (1995)5Comedy1509MLawyer
8Tom and Huck (1995)3Children's1573MStudent
9Sudden Death (1995)4Action1919MLawyer
10GoldenEye (1995)3Action1926FLawyer
11American PresidentThe (1995)3Drama2045FTeacher
12Dracula: Dead and Loving It (1995)5Horror2120MStudent
13Balto (1995)5Children's2169MStudent
14Nixon (1995)5Drama3234FLawyer
15Cutthroat Island (1995)5Action3594MLawyer
16Casino (1995)4Thriller3780MLawyer
17Sense and Sensibility (1995)4Drama4633MLawyer
18Four Rooms (1995)5Thriller4707MTeacher
19Ace Ventura: When Nature Calls (1995)5Comedy5074MLawyer
1 Solution

Accepted Solutions
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Jess,

Use set analysis, something like:

avg({<Occupation={"Lawyer"}>}Rating)

This part {<Occupation={"Lawyer"}>} is making the selection for lawyers only.

Felipe.

View solution in original post

13 Replies
felipedl
Partner - Specialist III
Partner - Specialist III

Hi Jess,

Use set analysis, something like:

avg({<Occupation={"Lawyer"}>}Rating)

This part {<Occupation={"Lawyer"}>} is making the selection for lawyers only.

Felipe.

Not applicable
Author

Hi Felipe

Thank you for helping. I'm sorry but my coding skills are bare minimal.

This is my code used but my bar chart is showing the wrong data.

Aggr(Avg({<Occupation={"Lawyer"}>}Rating),GenreCategory)

sunny_talwar

Do you need this in a KPI Object? May be this

=FirstSortedValue({<Occupation={'Lawyer'}>} GenreCategory, -Aggr(Avg({<Occupation={'Lawyer'}>}Rating), GenreCategory))

Not applicable
Author

Thank you Sunny. I'm not sure what a KPI Object is but I understand the firstsortvalue action.

I'm still not getting any data in my chart

sunny_talwar

What are the chart dimension where you are using this expression?

Not applicable
Author

It's GenreCategory (which would be the y-axis on my bar chart)

Not applicable
Author

I meant x-axis

sunny_talwar

Then use this as your expression as suggested by felipedl

avg({<Occupation={"Lawyer"}>}Rating)

Not applicable
Author

Can I check and clarify why Avg is used instead of Sum?

Should it not sum the total ratings for Lawyers?

Sum({<OccGroup={"Lawyer"}>}Rating)