Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
The double quotes are right, they need to be used as long as the expression does not return a number or a string, but a range, a search or it contains a wildcard.
Does the Deliverydate field store numeric values, comparable to the Num() returned in the expression (e.g.: Today() will return 43242)?
the deliverydate is in number format?
Hi Davy,
Would you be able to share a sample app?
Thanks,
RT
Deliverydate field is marked as 'mixed',
when I put in in a table it is in format 'DD/MM/YYYY' what is the same format as my result for =Today() and =YearStart(Today())
(but why did I in the past need to use num value?? maybe in the database this value has changed format?)
this is only small part of very large qvw so sorry I can't help you with that
something strange is also when I use
variable definition "=YearStart(Today())" => $(vYearStartToday) gives 0.0004955401
variable definition "YearStart(Today())" => $(vYearStartToday) gives 01/01/2018
variable definition "=num(YearStart(Today()))" => $(vYearStartToday) gives 43101
Makes sense:
Number 3 is very convenient because the numeric value does not change regardless the locale settings of the computer of the developer, the server or the user, and it's always faster to use numeric values instead of strings.
So back to your original issue, you need to make sure that both Deliverydate and the variable return the same format. You can create the Num() equivalent for Deliverydate already in the script, and use it for set analysis and comparisons, and use Deliverydate (date format) for representation in the charts.
This got me thinking and I’ve come up with something that works:
I let my variable come out in date format:
date(YearStart(Today()))
And I’ve added if() function in my set expression:
sum({1<OrderType={DELIVERIES}, Invoiced={'not yet'}>}
if(Deliverydate >= $(vYearStartToday), if(Deliverydate <= $(vYearEndToday), DeliveryValue)))
And it gives the correct result !! So thank a lot for your input !!
But I still don’t get why:
sum({1<OrderType={DELIVERIES}, Invoiced={'not yet'},
Deliverydate={">= $(vYearStartToday) <= $(vYearEndToday)"}>} DeliveryValue)
doesn’t give the same result… maybe it has something to do with the double quotes interfering with the variable retrieval
Best regards
Davy
Van: Miguel Angel Baeyens de Arce
Verzonden: donderdag 24 mei 2018 8:22
Aan: Davy Michiels
Onderwerp: Re: - Date range in set expression wrong since update
Just a hunch, try with
Deliverydate = {">=$(=Date(vYearStartToday))<= $(=Date(vYearEndToday))"}
or
Deliverydate = {">=$(=Date($(vYearStartToday)))<= $(=Date($(vYearEndToday)))"}
Depending on whether the variable has the leading "=".