topic Re: Set Analysis Dude in QlikView

Set Analysis Dude

angelaecheverri — Wed, 11 Dec 2013 18:16:47 GMT

Hi dears..

I have a problem with an expression..

I have one table which several kind of Invoices NB, CB, AF, NF...

I need sum all of excepting NF invoices... and resting NF invoices again

how can i do it?

In order to be more clear lets see next example:

Invoice	Value
NB	111
CB	222
AF	333
NF	11
NF	22

Total ALL invoices will be	699
ALL invoices descounting NF invoces type will be	666
Just NF invoices type will be	33
I am looking for this resault... (666-33)	633

I tryed:

sum({<Invoice =- {'NF'} - {'NF'}>}Value)

sum({<Invoice =- {'NF'}>-<Invoice = {'NF'}>}Value)

using P and E but it havent workt...

Re: Set Analysis Dude

MarcoARaymundo — Wed, 11 Dec 2013 18:59:59 GMT

I could not understand your question.

open the attachment to see what was done.

Re: Set Analysis Dude

Wed, 11 Dec 2013 19:13:01 GMT

Hola Angela,

Si lo que deseas es Sumar todos los valores de tu campo Value excepto los que sean Invoice = NF entonces puedes intentar lo siguiente:

sum({<Invoice=Invoice-{'NF'}>}Value)

Con esto sumas todo menos "NF".

Espero te sea de utilidad

Saludos.

(respondi en español pues vi que eres de colombia espero no equivocarme)

Re: Set Analysis Dude

angelaecheverri — Wed, 11 Dec 2013 19:16:23 GMT

Hola Oswaldo

Necesito sumar todas las formas mensos las NF y ademas restar el valor de las NF... como lo hago?

Re: Set Analysis Dude

jerem1234 — Wed, 11 Dec 2013 19:16:58 GMT

To get the result you want, you technically have to subtract the values for NF twice. So one sum with set analysis wont accomplish this, you'll have to use a second sum and subtract it from the first. This will give you a value of 633. If this isnt what you wanted please explain more. Please find attached.

Re: Set Analysis Dude

angelaecheverri — Wed, 11 Dec 2013 19:19:42 GMT

I don know how to sum all except NF and also how to sum just NF

what i need is sum all except NF and at the same expresion rest the value of just NF

How can I do it?

Re: Set Analysis Dude

angelaecheverri — Wed, 11 Dec 2013 19:22:53 GMT

Jerrem answer is quite near..

Can I do it in the same set fo Set Analysis... ?

Re: Set Analysis Dude

jaimeaguilar — Wed, 11 Dec 2013 19:25:59 GMT

Hi,

maybe...

sum( {$<Invoice -= {'NF'} >} Value) - sum( {$<Invoice = {'NF'} >} Value)

Re: Set Analysis Dude

jerem1234 — Wed, 11 Dec 2013 19:34:03 GMT

This formula:

sum({<Invoice-= {'NF'}>}Value)

sums everything except NF.

This formula:

sum({<Invoice= {'NF'}>}Value)

sums only NF.

What you are trying to accomplish is:

sum({<Invoice-= {'NF'}>}Value) - sum({<Invoice= {'NF'}>}Value)

which is the formula I used.

You cannot combine this any further with set analysis, since the first sum already excludes the values of NF. You can't exclude them again in set analysis. This is why you have to break out into 2 sums and subtract. You cannot do it in the same set for set analysis.

Hope this helps!

Re: Set Analysis Dude

Wed, 11 Dec 2013 20:33:43 GMT

Hola de nuevo,

Puedes Intentarlo así como Jaime te menciona o bien de esta manera:

sum({<Invoice=Invoice-{'NF'}>}Value) // Todos los valores excepto NF (666)

- // Menos

sum({<Invoice={'NF'}>}Value) // El valor de NF (33)

Resultado 633.

Espero esto sea lo que buscas.

Saludos.