topic aggr function: possible solution? in QlikView

aggr function: possible solution?

Thu, 28 Jan 2010 14:46:32 GMT

Hi guys,

I have a question which I believe can be solved using the aggr fucntion, but I'm a littlle unsure of how to apply. I have an expression:

(count ({$<ResponseText = {"Compliant"}>} ResponseText) + count ({$<ResponseText = {"Not applicable"}>} ResponseText)) / (count(System) *38)*100

I have a table in which I have a column listing all systems and the corresponding score arising from the expression noted above. I have also created another dimension in which if the score = 100, 'Yes', if score < 100, it returns 'No'.

However, i want to have a table which shows the number of '100 scored' systems within each unit. the problem when i replace system with unit in the table is that it takes a single figure average of all the system scores within that unit. so if 10 systems had a score of 100 and 10 had a score of 50 it would give a singular score of 75 for that unit. Is there a way in which i can count the number of systems in that unit that have a score of 100 using the aggr function?

I appreciate your help on this. thanks.

aggr function: possible solution?

Thu, 28 Jan 2010 21:36:48 GMT

i'm not sure you need AGGR

why not sum( if ( score = 100 , 1 , 0 )) using Unit as dimension.

but maybe i don't understand the structure of your data. could you post a sample, if it doesn't work.

aggr function: possible solution?

Fri, 29 Jan 2010 10:33:25 GMT

Hi,

The problem is....when I try to do this, the average score of the whole unit is applied. So this may be 95% and therefore a 0 will be applied to the whole unit. What I require is, the score of each system to be taken into account within a table only displaying the unit.

aggr function: possible solution?

Tue, 02 Feb 2010 13:07:03 GMT

Just to make it clearer, can create a table like this:

Unit	System	Score	if(Score = 100, 1,0 )
A	1	100	1
A	2	100	1
A	3	98	0
A	4	98	0
A	5	100	1

If I remove System as a dimension and I add a sum(if(Score = 100, 1,0)), it takes an average of the scores i.e. (100+100+98+98+100)/5 and therefore returns 0 as a value where I would like it to be:

Unit	SUM(if(Score = 100, 1,0 ))
A	3

Any thoughts on how to achieve this?

I appreciate all help.

aggr function: possible solution?

lironbaram — Tue, 02 Feb 2010 13:23:25 GMT

hi,

you are right , you need to use aggr function

the expression should look like this

=sum(aggr(if(score=100,1,0),system))

i attached an example too.

hope it helps.

aggr function: possible solution?

Tue, 02 Feb 2010 13:39:11 GMT

Great,

Thanks for the help, that sorted it out! 🙂