Set Analysis and If bringing back different values

[#1]

I have a situation where I have been using the expression =Sum({$} if(AssAssignmentDate <=  Date(Today()), AssCharge)) and I wanted to look at changing this to a Set Analysis throughout my document to gain better performance.  I have the formula =Sum({$} if(AssAssignmentDate <=  Date(Today()), AssCharge)) in a text box which brings back a value of $134,286,198.50 Next to this I have another text box with the following formula =Sum({$<AssAssignmentDate={"<=$(Date(Today()))"}>}AssCharge) and this brings back a value of $134,291,742.41

I do not have any selections made in my document at all.

I'm really confused as to why these values are different.  Any thoughts would be greatly appreciated.

Ryan

[swuehl]

Isn't there an equal sign missing in the dollar sign expansion?

=Sum({$<AssAssignmentDate={"<=$(=Date(Today()))"}>}AssCharge)

[#3]

I think that is only if you are referencing a variable, i I add an equal sign it comes back with a total of zero instead

[swuehl]

You'll need the equal sign to evaluate the expression.

If you remove it, I think you'll get the same result as when not using the set analysis filter at all (you can double check this).

If you get zero with an added equal sign, there seems to be an issue with the format of the field vs. format of your expression in the modifier.

What is your default date format and what is the format of field AssAssignmentDate?

[#5]

Yes you are correct, thank you.  If I remove the modifier I get the same result as having the modifier, so the equals is needed

My date was a timestamp but I set to the following in my script adapter -   Floor(date(Timestamp(AssignmentDate), 'DD/MM/YYYY')) as AssAssignmentDate

[swuehl · Accepted solution]

That probably removed the date formatting from your field, and there seems to happen some unnecessary formatting.

Have you checked that's not a numeric value without date formatting?

try

=Sum({$<AssAssignmentDate={"<=$(=Num(Today()))"}>}AssCharge)

[#7]

Perfect, that worked.  Thank you so much for your help.