topic Set Analisys in QlikView

Set Analisys

tizianacaem — Mon, 22 Feb 2010 16:08:51 GMT

Hi all,

I'm having some problems with the syntax of a set analisys expression, someone can help me solve it?

I have to consider the value of System_Net_Amount of the only fields including in both groups: Clienti and Clienti_new, is correct if i write:

sum ({Clienti*Clienti_new} System_Net_Amount)??

Thanks

Tiziana

Set Analisys

Miguel_Angel_Baeyens — Mon, 22 Feb 2010 16:15:58 GMT

Hello Tiziana,

If you mean you only want to sum System_Net_Amount by both fields, I'd rather use

Sum(<Clienti, Clienti_new> System_Net_Amount)

not needing set analysis.

Set Analisys

tizianacaem — Mon, 22 Feb 2010 16:42:33 GMT

I would get the sum of System_Net_Amount only of fields that are both in group Clienti than in group Clienti_New.

For example if ABC is an element of Cliente and also an element of Clienti_New, i want that his System_Net_Amount is counted, on the countrary if BCD is only a Cliente but not a Cliente_new i don't want his System_Net_Amount .

I hope I explained myself better,

Thanks

Set Analisys

Mon, 22 Feb 2010 16:46:12 GMT

Then maybe what you need is SUM(IF(Cliente<>NULL AND Cliente_new<>NULL,System_Net_Amount))

This will give you only the sum for records where the fields Cliente and Cliente_new have a value.

Alternatively you could work with len(Cliente)>0 or similar if the above does not work for you.

Cheers, Lukas

Set Analisys

jagan — Mon, 22 Feb 2010 16:47:25 GMT

If you mean you only want to sum System_Net_Amount by both fields, then use the expression

Sum(<Clienti, Clienti_new> System_Net_Amount)

or if u need the value in both the columns Clienti and Clienti_new are same and those values should be summed up by using the expression

 =sum(if(a=b,c,0))

Set Analisys

Miguel_Angel_Baeyens — Mon, 22 Feb 2010 16:53:10 GMT

If I have understood now, there is a very interesting approach here. Official documentation says that, anyway, you can just equal one field to another in set analysis, provided both fields cointain just a few values (below two hundred) otherwise calculations would affect performance severly. Syntax would be (for test purposes)

Sum({< Clienti = Clienti_new >} System_Net_Amount)

But i would give a try to the code in the mentioned post.

Set Analisys

Mon, 22 Feb 2010 17:05:39 GMT

Hi,

Try this,

sum({$<Group={'Clienti'}> * $<Group={'Clienti_new'}>} System_Net_Amount)

Hope it helps.