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: 
mlarruda
Creator
Creator

How can I plot an individual vs a group series?

Hi, friends. Suppose that my data has three columns/variables: NAME, MONTH and SCORE. So, each row means that that person had that score in that month.

So, to plot a graphic of score per person per month I use MONTH and NAME as dimensions and SCORE as expression and it works fine. But now I want to compare one specific person (eg. John) with the overall average, but I don't know how to do it.

Anyone can help me to build this graphic (one line being John and other being the overall average)?

Many thanks in advance.

Labels (3)
1 Solution

Accepted Solutions
mlarruda
Creator
Creator
Author

After try some ideas, I did it! Probably it is not the best way to do it, but it worked!

I defined, in the loading script, this variable:

SubField(IF(WildMatch(NAME,'*John*'),'Group 1','-')&'///'&
                    IF(WildMatch(NAME,'*John*','*Mary*','*Ben*','*Paul*'),'Group 2','-'),'///') as Group

and then, I did a table of Group values and selected Group 1 and Group 2 (to exclude any "-").

Lastly, I did a line graph with Month and Group as dimensions and this formula as Expression:

If(Group = 'Group 1', SUM(SCORE)/1, SUM(SCORE)/4).

Note that this allow me to include/exclude whoever I want in/from Group 1 and Group 2. It's only needed to adjust accordingly the denominators in the Expression formula.

View solution in original post

3 Replies
lironbaram
Partner - Master III
Partner - Master III

hi 

well you can do it like this 

create a line chart - 

Dimension : Month

Expression 1 : avg(Score) - this will display the result of the selected Name , you can set the label to =Only(Name) 

Expression 2 : avg({<Name=e(Name)>}Score) - this will calculate the avg of all other values in Name field

mlarruda
Creator
Creator
Author

Thank you, but I don't see how Avg(Score) will be restricted to the Name I want to select.

Also, about the 2nd expression, I wanted the average for ALL Names (not excluding the one I wanto to select).

mlarruda
Creator
Creator
Author

After try some ideas, I did it! Probably it is not the best way to do it, but it worked!

I defined, in the loading script, this variable:

SubField(IF(WildMatch(NAME,'*John*'),'Group 1','-')&'///'&
                    IF(WildMatch(NAME,'*John*','*Mary*','*Ben*','*Paul*'),'Group 2','-'),'///') as Group

and then, I did a table of Group values and selected Group 1 and Group 2 (to exclude any "-").

Lastly, I did a line graph with Month and Group as dimensions and this formula as Expression:

If(Group = 'Group 1', SUM(SCORE)/1, SUM(SCORE)/4).

Note that this allow me to include/exclude whoever I want in/from Group 1 and Group 2. It's only needed to adjust accordingly the denominators in the Expression formula.