Range of dates

[Mihail_RIX]

Hello!

I have a syntaxis problem which I cannot figure out how to deal with. I need an expression that sums the values for a range starting 2 days earlier than the same month in the previous year, and ending 2 days earlier than today in the previous year.

My expression looks like this:

Sum({<[BY_DATES] = {>=$(=Date(AddYears(MakeDate(Year(Today()), Month(Today()), 1),-1)-2)) <=$(=Date(AddYears(Max([BY_DATES]), -1) - 2))}>} [SALES])

A range is defined correctly, but there is something I still missing....

[BrunPierre]

Ensure that the dates are formatted consistently to match the format of your [BY_DATES] field.

[Mihail_RIX]

They are correct. ''Edit expression" has recognized it

[Chanty4u]

Try this 

Sum({<BY_DATES = {">=$(=Date(AddYears(MakeDate(Year(Today()), Month(Today()), 1), -1) - 2, 'YYYY-MM-DD')) <=$(=Date(AddYears(Today(), -1) - 2, 'YYYY-MM-DD'))"}>} SALES)

[marcus_sommer · Accepted solution]

You may try:

Sum({<[BY_DATES] =
   {">=$(=monthstart(Today(), -12) -2)<=$(=AddYears(Max([BY_DATES]), -1) - 2)"}>}
[SALES])

Important is that [BY_DATES] is a numeric date and not a timestamp (will in general be working but may return unexpected results) or even a string and that the condition is wrapped with double-quotes to be recognized as a search. Further by a >= <= comparing a numerical comparison is applied which means no format-interpretations is needed and the date-stuff could be skipped.