topic Re: set analysis in QlikView

set analysis

Wed, 17 Jul 2013 20:34:29 GMT

Hi All,

I have the pulled the below information from my database. The below have 2 types Invoce and recovery . Currently the database saves both as +ve.

provider	account	sp	type	sum(spamount)
abc	pqe	201110	Invoice	1143.8132
abc	pqe	201111	Invoice	1143.8132
abc	pqe	201112	Recovery	1143.8132
abc	pqe	201112	Recovery	946.118

I am trying to have an expression which can save the recovery as positive value and invoice as negative. Can any one please help me with the expression using set analysis.

Appreciate your time for reading the question and thanks in advance for helping.

Regards

Viral

Re: set analysis

jpapador — Wed, 17 Jul 2013 20:39:42 GMT

I would use an if statement instead of set analysis.

If(Type = 'Invoice', Sum(spamount) * -1, Sum(spamount))

Alternativley you could also in the load script do the same thing.

If(Type = 'Invoice', spamount * -1, spamount) as NewAmountName

Re: set analysis

swuehl — Wed, 17 Jul 2013 20:43:44 GMT

Maybe

=sum({<type = {Recovery}>} spamount) - sum({<type = {Invoice}>} spamount)

But why not store the data accordingly when loading in:

LOAD

if(type = 'Invoice', -1 * spamount, spamount) as spamount,

...