topic Re: Date range in set expression wrong since update in QlikView

Date range in set expression wrong since update

davymichiels — Wed, 23 May 2018 15:06:33 GMT

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

Re: Date range in set expression wrong since update

Miguel_Angel_Baeyens — Wed, 23 May 2018 15:09:46 GMT

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

Re: Date range in set expression wrong since update

sunny_talwar — Wed, 23 May 2018 15:10:22 GMT

the deliverydate is in number format?

Re: Date range in set expression wrong since update

Neymar_Jr — Wed, 23 May 2018 15:27:56 GMT

Hi Davy,

Would you be able to share a sample app?

Thanks,

Re: Date range in set expression wrong since update

davymichiels — Wed, 23 May 2018 15:45:48 GMT

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

Re: Date range in set expression wrong since update

davymichiels — Wed, 23 May 2018 15:46:37 GMT

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

Re: Date range in set expression wrong since update

davymichiels — Wed, 23 May 2018 15:54:27 GMT

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

Re: Date range in set expression wrong since update

Miguel_Angel_Baeyens — Thu, 24 May 2018 06:21:24 GMT

Makes sense:

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
The second returns what the function returns, which is a date format
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.

Re: Date range in set expression wrong since update

davymichiels — Thu, 24 May 2018 07:35:48 GMT

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

Re: Date range in set expression wrong since update

Miguel_Angel_Baeyens — Thu, 24 May 2018 07:59:13 GMT

Just a hunch, try with

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

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

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

Re: Date range in set expression wrong since update

davymichiels — Thu, 24 May 2018 08:25:36 GMT

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

Re: Date range in set expression wrong since update

Miguel_Angel_Baeyens — Thu, 24 May 2018 08:36:03 GMT

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

Re: Date range in set expression wrong since update

davymichiels — Thu, 24 May 2018 08:58:12 GMT

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!

Re: Date range in set expression wrong since update

Miguel_Angel_Baeyens — Thu, 24 May 2018 09:42:48 GMT

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