topic Re: How can I plot an individual vs a group series? in QlikView

How can I plot an individual vs a group series?

mlarruda — Sat, 16 Nov 2024 19:43:30 GMT

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.

Re: How can I plot an individual vs a group series?

lironbaram — Thu, 07 Nov 2019 18:44:15 GMT

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

Re: How can I plot an individual vs a group series?

mlarruda — Thu, 07 Nov 2019 18:49:23 GMT

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

Re: How can I plot an individual vs a group series?

mlarruda — Fri, 08 Nov 2019 13:21:47 GMT

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.