Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I tried the flag and it did work that way. Thank you. Curious on why it doesn't work the other way.
It works for the same reason the "if" expression works. As for the "set" - I don't see an easy way here, and recommend the flag or "if".
I am using the flag and was able to integrate it into my file fine. Thank you all for your help.
Here's the sample load:
tmp:NoConcatenate
load * inline
[_RecordID,fld1,fld2,balance
1,1,1,10
2,1,2,10
3,2,2,20
4,3,2,10
5,4,2,5
6,5,5,5
];
_RecordID is unique for each row in the table.
Here's the chat expression:
sum({$<[_RecordID]={"=fld1<>fld2"}>}balance)
Explanation: Return the _RecordID, when fld1 is not equal to fld2, then sum the balance.
This compare the two field and return _RecordID, which identify balance value on that row.
This works great as longa s _RecordID is unique, otherwise the return will find all _RecordID matching and sum its balance.
sum({$<UID= {"=PrevWAC <>CurrWAC"}>}Balance)
replace UID with your table's unique row id.