Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
davymichiels
New Contributor

Date range in set expression wrong since update

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

13 Replies

Re: Date range in set expression wrong since update

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

the deliverydate is in number format?

rajeshthakur292
Contributor II

Re: Date range in set expression wrong since update

Hi Davy,

Would you be able to share a sample app?

Thanks,

RT

davymichiels
New Contributor

Re: Date range in set expression wrong since update

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
New Contributor

Re: Date range in set expression wrong since update

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

davymichiels
New Contributor

Re: Date range in set expression wrong since update

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

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
New Contributor

Re: Date range in set expression wrong since update

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

Just a hunch, try with

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

or

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

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