topic Re: Filtering with set analysis in QlikView

Filtering with set analysis

Thu, 28 Feb 2013 15:45:02 GMT

I have a problem with the usage of set analysis. I have the following Pivot Table:

The Result 2 Field, calculates the sum of the field "z_dauer". Here the formula "sum(z_dauer)". It is calculating the total sum for the field "mitarbeiter_id" in this case. For instance the "mitarbeiter_id" 296 has total z_dauer of 4537,75, 317 has 3958,5 and so on. I want to filter this depending on the field "projektnummer".

In the Result 1 column, I used :

sum({$<z_projektnummer = {M112082} >} z_dauer)

This way, Result 1 filters the z_dauer with the value "M112082".

In the Result 3 column, I used:

sum({$<z_projektnummer = {$(=projektnummer)} >} z_dauer)

But unfortunately this does not work.

How can I tell my code to filter depending on Column projektnummer in this case?

Thanks for any help.

Re: Filtering with set analysis

Thu, 28 Feb 2013 18:00:33 GMT

I think that what you want is:

sum{$<z_projektnummer = projektnummer >} z_dauer).

Re: Filtering with set analysis

Thu, 28 Feb 2013 19:32:53 GMT

Unfortunately it is not working. I guess you forget the opening column so I wrote:

sum({$<z_projektnummer = projektnummer >} z_dauer)

But not working.

Re: Filtering with set analysis

Thu, 28 Feb 2013 19:50:42 GMT

Hi,

Does this work?

sum(if(z_projektnummer = projektnummer, z_dauer))

Re: Filtering with set analysis

Thu, 28 Feb 2013 19:59:32 GMT

Try sum({$z_projektnummer = {$<projektnummer>} >} z_dauer), then? I'm just going off of the Set Analysis topic in QV Help, but... that might be it?

Re: Filtering with set analysis

Thu, 28 Feb 2013 20:01:32 GMT

With this expression it works. If I understand it correctly, I can take the values of the dimension, in this example =projektnummer and use it as a WHERE clause, so that I can filter the expression with a field not existent in the table ( z_projektnummer).

It is like SELECT sum(z_dauer) FROM table Z WHERE z_projektnummer IN (projektnummer)

which projektnummer is filled with the dimension row values.

Re: Filtering with set analysis

Thu, 28 Feb 2013 20:03:16 GMT

I tried that too:) But the part {$<projektnummer>} does not give the values of the dimension...