topic Set Analysis Within Aggr Functions in QlikView

Set Analysis Within Aggr Functions

Mon, 22 Mar 2010 12:04:19 GMT

Hi guys,

I have a requirement from a customer in which we would like a table to be unaffected by any selection (basically going against Qlikview methodology!). The trouble I am having is that I can't seem to implement set analysis into my aggr function. I have created a simple(ish) example:

Unit	A	B	C	D
Total	20	30	40	50
Compliant	10	8	12	10
Not Compliant	10	22	28	40

We have units A,B,C,D and the corresponding total, number of complaint systems and number of not compliant systems within a unit. These calcs are created using aggr functions involving counting the number of compliant systems within a unit as follows:

=sum({$< Unit = >} aggr(if(Status = 'LtO Compliant',1,0),System))

Howvever if I now select, for example unit A, all the figures for the rest of the table return 0. Is there any way in which this can be overcome?

Thanks for the help.

Set Analysis Within Aggr Functions

Mon, 22 Mar 2010 12:06:08 GMT

Hi there,

basically your set analysis should be based on "1" and not on "$", as you want to work on all the records as opposed to the current selection only.

Cheers, Lukas

Set Analysis Within Aggr Functions

Mon, 22 Mar 2010 12:12:32 GMT

Thanks Lukas,

I changed my expression to :

=sum({1< Unit = >} aggr(if(Status = 'LtO Compliant',1,0),System))

but unfortunately it still produced the same results.

Set Analysis Within Aggr Functions

Tue, 23 Mar 2010 10:53:54 GMT

Hi everyone,

Just wondering if anyone had some other ideas to this problem?

Any comments would be welcome,

Thanks

Set Analysis Within Aggr Functions

michael_anthony — Tue, 23 Mar 2010 12:08:11 GMT

I don't really use Aggr. My understanding was that you usually put an aggregation function within the Aggr function but you don't have one, just a 1 / 0 calculation.

But I'd suggest trying also putting the set extension {1<Unit = >} within the aggr function.

sum({1< Unit = >} aggr({1< Unit = >} if(Status = 'LtO Compliant',1,0),System))

May have to try some other placements, eg. within the If statement.

Set Analysis Within Aggr Functions

Tue, 23 Mar 2010 12:34:00 GMT

Hi, could you perhaps post a sample QVW, that yould make things easier. Thanks.

Set Analysis Within Aggr Functions

Mon, 14 Feb 2011 17:11:41 GMT

Hi,

Was this solved ... you could perhaps detach the table ... that will remove it from being included in selections I believe.