topic How to get this set analysis into aggr expression(nested) in QlikView

How to get this set analysis into aggr expression(nested)

Thu, 22 Sep 2011 15:17:32 GMT

Hi,

I have the expression

sum(aggr([Actual value]*[Number of Respondents],[Question number],[Institution],[Subject],[Level])) / sum([Number of Respondents]) which works, but I now need to incorporate this simple set analysis into it : {$<Institution={'*'}>}

I'm unsure of which order it needs to be in and seem to be going around in circles.

Any ideas on how it can be changed to bring the two expressions into one.?

Thank-you!

How to get this set analysis into aggr expression(nested)

Jason_Michaelides — Thu, 22 Sep 2011 15:53:12 GMT

Have you tried:

sum({$<Institution={'*'}>} aggr([Actual value]*[Number of Respondents],[Question number],[Institution],[Subject],[Level])) / sum([Number of Respondents])

Jason

How to get this set analysis into aggr expression(nested)

Thu, 22 Sep 2011 16:00:37 GMT

Hi Jason,

I tried this but it give me the same result as :

sum(aggr([Actual value]*[Number of Respondents],[Question number],[Institution],[Subject],[Level]))/

sum([Number of Respondents])

How to get this set analysis into aggr expression(nested)

Jason_Michaelides — Thu, 22 Sep 2011 16:02:53 GMT

What are you looking for the expression to do exactly?

How to get this set analysis into aggr expression(nested)

Thu, 22 Sep 2011 16:10:19 GMT

So the first expression works out the Average, for what ever is selected in the app.

Then with the second expression, I want to give an Average for all Institutions, regardless of which one is selected.

ie. {$<Institution={'*'}>}

How to get this set analysis into aggr expression(nested)

Jason_Michaelides — Thu, 22 Sep 2011 16:12:36 GMT

Ah - try this:

sum({$<Institution= >} aggr([Actual value]*[Number of Respondents],[Question number],[Institution],[Subject],[Level])) / sum([Number of Respondents])

How to get this set analysis into aggr expression(nested)

Thu, 22 Sep 2011 16:19:56 GMT

It's really odd, because both expressions give the same values.

I'm going to attach the sample I'm working on to the original post

How to get this set analysis into aggr expression(nested)

Jason_Michaelides — Thu, 22 Sep 2011 16:21:20 GMT

You may need to remove "Institution" from the Aggr() function.

How to get this set analysis into aggr expression(nested)

Thu, 22 Sep 2011 16:26:32 GMT

I also tried that, but strangely it still gives the same values for both expressions..

How to get this set analysis into aggr expression(nested)

Jason_Michaelides — Thu, 22 Sep 2011 16:28:32 GMT

I just had another look and think you need to add the set analysis into the second half of the equation too (although the number gets very small then!):

sum({$<Institution= >} aggr([Actual value]*[Number of Respondents],[Question number],[Institution],[Subject],[Level])) / sum({$<Institution= >} [Number of Respondents])

How to get this set analysis into aggr expression(nested)

Thu, 22 Sep 2011 16:34:35 GMT

Not much luck.

How to get this set analysis into aggr expression(nested)

Jason_Michaelides — Thu, 22 Sep 2011 16:36:20 GMT

To be honest I was guessing! Challenging one - I don't use Aggr() much.

How to get this set analysis into aggr expression(nested)

Thu, 22 Sep 2011 16:46:53 GMT

I'll carry on at it. It appears so simple though..hmmm.

How to get this set analysis into aggr expression(nested)

Jason_Michaelides — Thu, 22 Sep 2011 16:49:38 GMT

I'm looking at it and wondering if the problem isn't in the data model. What does "Number of respondents" mean? Should it be in the Fact table with Question number? Or is it a number of respondents per question? Can you give a little background on the data.

(I'm hooked on this now...)

How to get this set analysis into aggr expression(nested)

Thu, 22 Sep 2011 16:58:49 GMT

It's basically student data with the Question Number being a key in the fact table, along with:

Institution, Subject, Level, [Question number], [Number of Respondents] and [Actual value] etc. etc.

The Global Average used to just be the simple expression : //AVG({$<Institution={'*'}>}[Actual value]), which needed to be changed, as it wasn't giving the correct values.

Re: How to get this set analysis into aggr expression(nested)

Jason_Michaelides — Thu, 22 Sep 2011 17:04:28 GMT

So does this record mean that 19 "Other undergraduates" studying Business Studies in the selected institution answered 63% for Q18?

How to get this set analysis into aggr expression(nested)

Thu, 22 Sep 2011 17:10:17 GMT

Yes, that's exactly what it's saying.

How to get this set analysis into aggr expression(nested)

Jason_Michaelides — Thu, 22 Sep 2011 17:25:37 GMT

Sorry, just to be clear - did 19 students get Q18 63% correct or was the answer given by 19 students '63%'?

How to get this set analysis into aggr expression(nested)

Thu, 22 Sep 2011 17:53:19 GMT

Hi Jason,

Apologies for not being clear.

So, it's saying that Q18 was answered(given) by 19 students.

How to get this set analysis into aggr expression(nested)

Jason_Michaelides — Thu, 22 Sep 2011 17:56:21 GMT

And 63% of those 19 students got it right?