Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
nicktodd
Creator
Creator

Basic Scatter diagram/set analysis question

I made a small app (simplified version attached) to display student marks for a module versus their average across all modules - calculated by set analysis function


Avg({$<Module=>} Mark)

The issue is that, because of the set analysis function, in selecting a module (for example, M4) all students are shown on the diagram legend even though some students (E and F) did not take that module. I'd like to exclude students who didn't take the module so that a user may export the scatter diagram to Excel and see students for that module only.

I've a feeling there must be a very simple solution to this but I can't see it!

Thanks

1 Solution

Accepted Solutions
nicktodd
Creator
Creator
Author

As I suspected the answer was staring me in the face!

All I did was:

=if(not IsNull(Mark),Avg({$<Module=>} Mark))

and now I get the right answer for only those students studying a module. Doh!

Nick

View solution in original post

10 Replies
kiranmanoharrode
Creator III
Creator III

Hi Nick,

     Try this expression in your average expression, it will work


=Avg(agar(Avg({1<Module=>} Mark),Module))

Regards,

Kiran

Not applicable

Hi,

PFA. Hope it helps!

Edit : Just change your expression for Average to :

  =Avg({$<Module = P({<Student>} Module)>} Mark)

nicktodd
Creator
Creator
Author

Shyamal,

Thanks! I had a play around with the P function before but got nowhere with it - now I know!

Nick

nicktodd
Creator
Creator
Author

Shyamal

Seems I spoke too soon! Although your solution restricts the students displayed it appears to give the wrong answer!


See the attached to show what I mean, giving the new logic on the left and the old logic on the right.

Nick

nicktodd
Creator
Creator
Author

Kiran

I tried this but couldn't get it to work - should that be 'aggr'?

Thanks

Not applicable

Nick

PFA. Missed 1. Check the expression in the chart on left.

So, your expression should be :

=Avg({$<Module = P({1<Student>} Module)>} Mark)


Hope this one helps!

qlikpahadi07
Specialist
Specialist

Hi Todd,

I am not sure why you are using set analysis ....

for Avg I have made a Simple chart please look at it

nicktodd
Creator
Creator
Author

Shyamal

Thanks again for your response. Your updated statement of


=Avg({$<Module = P({1<Student>} Module)>} Mark)

gives the correct answer in terms of plotting the points on the scatter chart - unfortunately it has the same issue as my original statement of

Avg({$<Module=>} Mark)

In that students who aren't taking the module are still shown in the legend and on the Excel extract if you write the results to Excel. For example, for module M4, Students E and F are shown even though they didn't take M4.

Regards

Nick

nicktodd
Creator
Creator
Author

QlikPahadi07

Thanks for your response. The idea is to show on a scatter diagrams students marks for a module (Y axis) against the average for all modules (X axis) - hence points plotted above the diagonal are easier than average, points plotted below the line are harder than average. Your solution is plotting the points on the line, which is not what we want.

Regards

Nick