5 Replies Latest reply on Apr 20, 2012 6:22 AM by justin.goodrich

sum(iif([date]==today(),[arbitrary variable 2],0))

[date] is a timestamp and I'm not getting the expected result i.e not receiving all orders for today with the following formula.

sum(iif([date]==today(),[arbitrary variable 2],0))

Does anyone see an issue with the formula. I've vetted with no avail.

Thank you.

• 1. Re: sum(iif([date]==today(),[arbitrary variable 2],0))

TODAY() is defined as NOW() rounded to the start of today, so that's the first issue since [date] is a datetime and not a date.

Beyond that, it's possible that basic datetime equality comparison might run into floating-point comparison issues under the hood.  You could experiment with using the DATEPART functions to compare the year, month, and day separately, and see if that fixes anything.  Let me know.

• 2. Re: sum(iif([date]==today(),[arbitrary variable 2],0))

Just to expand James's comment, you probably need:

sum(iif(DATETRUNC('day',[date])==today(),[arbitrary variable 2],0))

• 3. Re: sum(iif([date]==today(),[arbitrary variable 2],0))

Oh yeah, DATETRUNC. That's better.

• 4. Re: sum(iif([date]==today(),[arbitrary variable 2],0))

Thank you guys. Going to test shortly and will let you know how it works.

• 5. Re: sum(iif([date]==today(),[arbitrary variable 2],0))

Really appreciate the help. This appeared to do the trick. If you can direct me to any reference material for more advanced formula help for functions like this I would greatly appreciate it.  Thank you again.