topic Re: Set Analysis including all but selected field in QlikView

Set Analysis including all but selected field

zagzebski — Fri, 15 Mar 2013 17:56:39 GMT

(1) What function do I use in an expression to get the total of everything EXCEPT the selected field?

For instance if I simply wanted to sum revenue but for all clients except the one I have selected what is the set analysis for that?

(2) Similarily to this if I have a client that is part of heirachy, how do I get the total of the heirachy for the client chosen?

Example Client A is part of the Auto Division, Client B is part of the Home Division, etc

When I select Client A how to I get the total revenue for the Auto Division? then if I select client B I get the total revenue of the Home Division?

Thanks in advance...

Fri, 15 Mar 2013 18:02:17 GMT

Hi,

Try with this:

Only({< Dim={'*'}-{$(=GetFieldSelections(Dim))} >} Dim).

See the attached.

Regards,

Ricardo

zagzebski — Fri, 15 Mar 2013 18:41:51 GMT

Thanks for the assistance.

Here is what I have in my expression but nothing is coming back...

sum(Only({< [Product-Entity]={'*'}-{$(=GetFieldSelections(Product-Entity))} >} [Product-Entity]),[Revenue])

Anonymous — Fri, 15 Mar 2013 18:50:28 GMT

Try using E(), which means "Excluded":

sum({<Product-Entity]=E([Product-Entity])>} Revenue)

Regards,

Michael

zagzebski — Fri, 15 Mar 2013 19:01:32 GMT

Thanks!

Is it possible to do this:

If I have a client that is part of heirachy, how do I get the total of the heirachy for the client chosen?

Example Client A is part of the Auto Division, Client B is part of the Home Division, etc

When I select Client A how to I get the total revenue for the Auto Division? then if I select client B I get the total revenue of the Home Division?

Is this possible?

Anonymous — Fri, 15 Mar 2013 19:33:06 GMT

Everything's possible 🙂

Try this:

sum({<Client=, Division=P(Division)>} Revenue)

zagzebski — Mon, 18 Mar 2013 18:48:01 GMT

Perfect - thanks Michael.