Date range in set expression wrong since update

[davymichiels]

Hi everyone,

Since updating to November release 2017 SR4 I'm experiencing difficulties with some set expressions in text fields and chart expressions...

I believe this might have something to do with the single quote and double quote changes due to this upgrade but still I'm not finding the correct solution for the daterange-part:

Old formula =

sum({1<OrderType={DELIVERIES}, Invoiced={'no yet'}, Deliverydate={'>=$(=num(YearStart(Today()),'#0'))<$(=num(YearEnd(Today()),'#0'))'}>} DeliveryValue)

In new formula I'm sure I need to make the single quotes now double quotes:

Deliverydate={">=$(=num(YearStart(Today()),'#0'))<$(=num(YearEnd(Today()),'#0'))"}

but that doesn't work...

Now I thought about storing YearStart and YearEnd in variables and using them:

vYearStartToday with definition as =YearStart(Today())

vYearEndToday with definition as = YearEnd(Today())

and changing the daterange-part in my set expression to:

Deliverydate={">= $(vYearStartToday) < $(vYearEndToday)"}

but still no luck...

even playing around with my variable definitions doesn't bring the right solution:

definition as =num(YearStart(Today()),'#0') or as YearStart(Today()) doesn't help

What am I missing here ?

BR,

Davy

[davymichiels]

Almost there ☺

- the first option doesn’t work

- the second option only works partly:

the first part is ok : only values with dates after start of this year

but the second part doesn’t get picked up as my results show values for 2019, meaning <= end of this year doesn’t work

[Miguel_Angel_Baeyens]

Then you will have to check that the variable gets the right date and that there is data for that time, the syntax is OK as long as both are defined the same way (both with leading "=" or both without, both with Num() or neither, etc.)

[davymichiels]

Finally:

1) no need to worry for date format of my variable: variable definition is simply YearStart(Today()) end YearEnd(Today())

2) your second formula works perfect after I made one simple adjustment: apparently there may not be a blank space between the <= and the following $-sign

     Deliverydate = {">=$(=Date($(vYearStartToday)))<=$(=Date($(vYearEndToday)))"}

I've never known blank spaces could pose a problem here!

[Miguel_Angel_Baeyens]

Glad it worked! Yep, blanks in search strings or literals count.