Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
davymichiels
Contributor
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
davymichiels
Contributor
Contributor
Author

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

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.