Set Analysis - Greater than the Current Date

[#1]

I would like to sum all records where the Record Type = 'Actual' and Date (this is the name of the column) is greater than the Current Date.

The set analysis I used always display a value of zero. I understand that the Date and Today() dates should be of the same format to get the desired result but I'm still unable to get the correct result even with formatting included.

Below is my code.

sum({$<[Record Type]={'Actual'}, Date = {"> $(=Date(Today(),'MM-DD-YYYY'))"}>} Dollars)

[johnw]

Looks right to me. The only thing I'm thinking is maybe it doesn't like the space after the greater than sign. I don't put a space there myself, but I don't know if that's just habit or a requirement.

[#3]

Hi John. Thanks for the reply.

I removed the space and the issue still exists. I think the problem is still in the date formatting. I'll try to figure out what sort of format it's expecting and probably use some variables while I'm at it.

[#4 · Accepted solution]

So the problem was the date format. I converted the Date to num(Date) as "Fiscal Date" in my Load Script and use that in my Set Analysis. So instead of formatting, I just used the number equivalent of the date and that did the trick. Really hate dealing with Date comparison in QV.

=sum({$<[Record Type]={'Actual'}, [Fiscal Date]= {"> $(=num(Today()))"}>} Dollars)