$ use doesn’t make sense to me

[ColdSpark]

I am trying to compare a column of dates (promoted) and count how many times the date in that field is greater than the start of the reporting year (01/01/2023 in this case)

if I run this:

if(date([promoted.autoCalendar.Date], ‘YYYY-MM-DD’) $(‘>=‘ & makedate(Reporting_Year,1,1)),1,0) 

it returns 1

if I run this though 

if(date([promoted.autoCalendar.Date], ‘YYYY-MM-DD’) >= makedate(Reporting_Year,1,1),1,0)

it returns 0

Can anyone tell me why? To me it should be the same outcome surely?

[Or]

The first version is not a valid expression. It'll still attempt to evaluate, but with the dollar sign expansion being invalid, it'll be the same as writing:

if(date([promoted.autoCalendar.Date], ‘YYYY-MM-DD’),1,0)

Which means the if will always evaluate to true unless the date is null or 0.

[jonashertz · Accepted solution]

Your first dollar expansion is not correct and returns just null. Thus the first if statement is actually only the date which is interpreted numerically, and not 0, so it evaluates to true.

The second expression looks correct so this would then return 0 if your date is not larger than the Reporting_year.

One correct way (of many) to make use of dollar expansion in your case is to move the >= out of the dollar expansion and use the syntax for dollar expansion using an expression, which is $(=expression()). Then add single quotes as well to make it a string otherwise it will just be calculated arithmetically as year - 1 -1 or year -2 and that is not what you want:

=if(date([promoted.autoCalendar.Date], ‘YYYY-MM-DD’) >= '$(=makedate(ReportingYear,1,1))',1,0)