Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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).
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.