Set Analysis with Field = Field

[#1]

I am trying to wirte a set analysis statement for the sum where two fields have equal values. 

I am using the syntax  sum({<PrevWAC -= CurrWAC>} Balance) this returns the value equal to sum(Balance) so it is ignoring the set statement.

If I use an If statement of If(PrevWAC <> CurrWAC, sum(Balance), 0) in a straight table with the dimensions of 'account', 'PrevWAC', 'CurrWAC' the table shows the sum where PrevWAC is not equal to the CurrWAC at the account level and the total sum of rows gives me the correct answer.

I cannot figure out why the If statement works and the Set Analysis does not.  I need to use Set Analysis because this is just one part of the statement I am trying to create.  I have searched the posts and tried every solution idea I could find.  Any Ideas?

Thank You

Patrick

[CELAMBARASAN]

Hi,

     you can try with this..

     sum({<PrevWAC -= {CurrWAC}>} Balance)

[Anonymous]

Patrick,

Your "set" is likely to include everything, if for each selection of PrevWAC there is value in CurrWAC which is not equal to PrevWAC.
Try this:
sum({-<PrevWAC = P(CurrWAC)>} Balance)

Regards,
Michael

[#4]

Thank you for the reply, but I did try that and it does not work. 

I have also tried

sum({<PrevWAC = P(CurrWAC)>}Balance)

[#5]

Is PrevWAC and CurrWAC in the same table? If it is it might be able to create a flag in the script.

something like:

if (PrevWAC<>CurrWAC,1) as Flag

then in the table

sum({<Flag={'1'}>} Balance)

[#6]

I tried sum({-<PrevWAC = P(CurrWAC>}Balance) which gave me a new number but the not the one I am looking for.

Each 'account' has one value for 'PrevWAC' and 'CurrWAC' .  I need the sum only for the accounts where PrevWAC and CurrWAC are the same. 

[#7]

PrevWAC and CurrWAC are in the same table. 

[Anonymous]

Now it is not quite clear...

Could you please post an example of data in table format, with columns PrevWAC, CurrWAC, Balance, and the desired result?

[#9]

To clarify there is an entry in the table for each month, for each account, that has a value for PrevWAC and CurrWAC.  When PrevWAC and CurrWAC are different on an entry I want to sum those balances for each month.  I have been including the month value in the set analysis, which works fine.  I hope the attachment helps. 

[#10 · Accepted solution]

DId you try making a flag like I suggested?

LOAD "month_date",

    MonthEnd("month_date") as EndOfMonth,

    account,

    CurrBal,

    BalChg,

    PrevWAC,

    CurrWAC,

     if(PrevWAC<>CurrWAC,1) as Flag;

SQL SELECT *

FROM SDM.dbo."cpi_roll_on_off"

where ProdGroup = 'ARM-4X1';

Then the Set analysis would just be:

sum({<Flag={'1'}>}CurrBal)