topic Re: Range of dates in App Development

Range of dates

Mihail_RIX — Thu, 20 Feb 2025 13:35:24 GMT

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....

Re: Range of dates

BrunPierre — Thu, 20 Feb 2025 13:52:06 GMT

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

Re: Range of dates

Mihail_RIX — Thu, 20 Feb 2025 14:11:18 GMT

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

Re: Range of dates

Chanty4u — Thu, 20 Feb 2025 18:11:03 GMT

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)

Re: Range of dates

marcus_sommer — Fri, 21 Feb 2025 07:23:46 GMT

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.