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

[Miguel_Angel_Baeyens]

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)?

[sunny_talwar]

the deliverydate is in number format?

[Neymar_Jr]

Hi Davy,

Would you be able to share a sample app?

Thanks,

RT

[davymichiels]

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?)

[davymichiels]

this is only small part of very large qvw so sorry I can't help you with that

[davymichiels]

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

[Miguel_Angel_Baeyens]

Makes sense:

1. The first one is taking the date as a formula and evaluating it, because of the leading "=": 1 divided by 1 divided by 2018 = 0.000495
2. The second returns what the function returns, which is a date format
3. Any date in QlikView and Qlik Sense has both a numeric representation, counting from day 1 which is 31/12/1899 and a string representation which is DD/MM/YYYY or whatever the format you have specified in the variables at the beginning of the script or by default, those of the operating system.

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.

[davymichiels]

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

[Miguel_Angel_Baeyens]

Just a hunch, try with

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

or

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

Depending on whether the variable has the leading "=".