topic Re: Basic Scatter diagram/set analysis question in QlikView

Basic Scatter diagram/set analysis question

nicktodd — Mon, 16 Dec 2013 09:03:46 GMT

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

Re: Basic Scatter diagram/set analysis question

kiranmanoharrode — Mon, 16 Dec 2013 09:21:57 GMT

Hi Nick,

Try this expression in your average expression, it will work

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

Regards,

Kiran

Re: Basic Scatter diagram/set analysis question

Mon, 16 Dec 2013 09:29:03 GMT

Hi,

PFA. Hope it helps!

Edit : Just change your expression for Average to :

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

Re: Basic Scatter diagram/set analysis question

nicktodd — Mon, 16 Dec 2013 09:42:52 GMT

Shyamal,

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

Nick

Re: Re: Basic Scatter diagram/set analysis question

nicktodd — Mon, 16 Dec 2013 11:59:21 GMT

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

Re: Basic Scatter diagram/set analysis question

nicktodd — Mon, 16 Dec 2013 12:10:11 GMT

Kiran

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

Thanks

Re: Re: Re: Basic Scatter diagram/set analysis question

Tue, 17 Dec 2013 05:01:11 GMT

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!

Re: Basic Scatter diagram/set analysis question

qlikpahadi07 — Tue, 17 Dec 2013 07:17:16 GMT

Hi Todd,

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

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

Re: Basic Scatter diagram/set analysis question

nicktodd — Tue, 17 Dec 2013 09:05:44 GMT

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

Re: Basic Scatter diagram/set analysis question

nicktodd — Tue, 17 Dec 2013 09:10:55 GMT

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

Re: Basic Scatter diagram/set analysis question

nicktodd — Tue, 17 Dec 2013 09:56:24 GMT

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