Set Analysis Question

[#1]

I have the formula listed below that provides me with Yesterday's Sales. Currently if filters are applied that number changes. I am trying to get the Text Object that contains this formula to only ever show Yesterday's Sales. I know I need to do this through Set Analysis but I can't seem to get anything to work. It either returns 0, an empty set or some outrageously large number that could never be correct.

='Yesterday' & chr(39) & 's Sales: ' & Money(Sum(If(Date=$(LastSalesDate),OrderTotal,0)))

Any suggestions?

[#2 · Accepted solution]

So, that formula works as long as you have no selections.

To ignore selections using Set Analysis, use:

='Yesterday' & chr(39) & 's Sales: ' & Money(Sum( {1} If(Date=$(LastSalesDate),OrderTotal,0)))

EDIT: The rest of the If can easily be put into the Set Analysis:

='Yesterday' & chr(39) & 's Sales: ' & Money(Sum( {1<Date={'$(LastSalesDate)'}OrderTotal))

I think that second one should work, but it may depend on what LastSalesDate is and your data format.