Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
Valued Contributor III

Re: Filtering out data

Hi Jess,

Use set analysis, something like:

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

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

Felipe.

13 Replies
felipedl
Valued Contributor III

Re: Filtering out data

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

Re: Filtering out data

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)

Re: Filtering out data

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

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

Not applicable

Re: Filtering out data

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

Re: Filtering out data

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

Not applicable

Re: Filtering out data

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

Not applicable

Re: Filtering out data

I meant x-axis

Re: Filtering out data

Then use this as your expression as suggested by felipedl

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

Not applicable

Re: Filtering out data

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)

Community Browser