topic Re: Daterange with a variable enddate in QlikView

Daterange with a variable enddate

Wed, 03 Aug 2016 13:50:11 GMT

Hello,

I need some help and hope you all can help me with my Problem.

First of all a litle description of my Problem:

I want to select all data in my database (startdate is for example 01.01.14) until the last day,2Monthe before today (today the last selected data would from the 30.06.16)

Here is my Syntax that gives me all data from startdate until today (no excluding from data from July 😞 )

Sum({< DATUM = {">=$(=01.01.2014)<=$(DATE(MonthEnd(today(),-2)))"} >} DATA)

It is a Patchwork Syntax from the Helptool from QlikView, some Topics from the Internet and my own creativity...

Regards Vince

Re: Daterange with a variable enddate

sunny_talwar — Wed, 03 Aug 2016 15:24:16 GMT

Try this:

Sum({<DATUM = {">=$(=Date(MakeDate(2014, 1, 1), 'DD.MM.YYYY'))<=$(=Date(MonthEnd(Today(), -2), 'DD.MM.YYYY'))"}>} DATA)

Re: Daterange with a variable enddate

sunny_talwar — Wed, 03 Aug 2016 15:24:58 GMT

Actually adding an equal sign might help with your current expression:

Sum({<DATUM = {">=$(=01.01.2014)<=$(=DATE(MonthEnd(today(),-2)))"} >} DATA)

Re: Daterange with a variable enddate

Thu, 04 Aug 2016 06:30:04 GMT

Hello,

sorry but your solutions dosn´t work...

1) The result of the first one is 0

2) The result of the second is all data with no exclude over the date 😞

I tryed to verify the Syntax with fix Dates like this:

Sum({< DATUM = {"$(= '>=' & '01.01.14' & '<=' & '31.12.15')"}>} DATA )

That Syntax dosn´t work either...

Is it possible that QlikView don´t understand the format of the date ore something like that !?

I am from Germany and we use DD.MM.YYYY... but basicly the Timecode is a nuber like "42586" for today, so in this case there is no Formatfailur possible or am i wrong ?

Re: Daterange with a variable enddate

sunny_talwar — Thu, 04 Aug 2016 10:34:43 GMT

How about this?

Sum({<DATUM = {">=$(=Num(MakeDate(2014, 1, 1)))<=$(=Num(MonthEnd(Today(), -2)))"}>} DATA)

Re: Daterange with a variable enddate

Fri, 05 Aug 2016 10:49:13 GMT

That one works perfect, thanks a lot!

Vince