topic Re: Set analysis needed in field aggr-function? in QlikView

Set analysis needed in field aggr-function?

Janneke — Mon, 10 Nov 2014 13:27:28 GMT

Hi all,

I'm running into a problem using the aggr-function in combination with set analysis. There are a number of selections possible, including YEAR. However, selection of a YEAR should not affect the outcome.

This is the expression I am using:

=aggr(NODISTINCT count({$<YEAR=>}ID),YEAR)

This is the outcome (selected YEAR is 2013):

GROUP YEAR #

A 2013 100

A 2014 -

B 2013 100

B 2014 -

This is what I would like to have (selected YEAR is 2013):

GROUP YEAR #

A 2013 100

A 2014 200

B 2013 100

B 2014 200

The expression is basically working, however it works only for the chosen YEAR. I think this has to do with the aggr-field YEAR that I use (without set analysis).

Could someone please help me? Thanks in advance!!

Regards, Janneke.

Re: Set analysis needed in field aggr-function?

MK_QSL — Mon, 10 Nov 2014 13:49:46 GMT

COUNT({<YEAR = >}ID)

Re: Set analysis needed in field aggr-function?

Janneke — Mon, 10 Nov 2014 14:00:23 GMT

Hi Manish,

This gives the wrong amount, I think because GROUP and ID are from different tables.

Greetings, Janneke.

Re: Set analysis needed in field aggr-function?

anbu1984 — Mon, 10 Nov 2014 14:05:34 GMT

Can you post sample qvw

Re: Set analysis needed in field aggr-function?

danielact — Mon, 10 Nov 2014 14:59:19 GMT

Try putting an Only({$<YEAR=>} around your aggr function.

Re: Re: Set analysis needed in field aggr-function?

Janneke — Mon, 10 Nov 2014 15:15:19 GMT

Thank you Daniel!!

=only({$<YEAR=>}aggr(NODISTINCT count({$<YEAR=>}ID),YEAR))

works perfectly.

Regards, Janneke.