Daterange with a variable enddate

[#1]

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

[sunny_talwar]

Try this:

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

[sunny_talwar]

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

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

[#4]

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 ?

[sunny_talwar · Accepted solution]

How about this?

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

[#6]

That one works perfect, thanks a lot!

Vince