set analysis

[#1]

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

[jpapador · Accepted solution]

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

[swuehl]

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,

     ...