topic Re: Restricting data based on greater than or equal to date in QlikView

Restricting data based on greater than or equal to date

Anonymous — Mon, 29 Jun 2015 08:53:47 GMT

Hi,

I have this code and I don't know why it doesn't work. It seems very simple but it just doesn't work. I want to sum the data if its greater than or equal to a date

sum({<ddis={'>=$(vMinDate)'}>}counter)

ddis is a date and vMinDate is a user selected date

I have tried with just = to the date and this works - sum({<ddis={'=$(vMinDate)'}>}counter)

Any ideas why when I use the >= it doesn't work?

Many thanks,
Emer

Re: Restricting data based on greater than or equal to date

henrybrinkmann — Mon, 29 Jun 2015 08:57:05 GMT

Hi Emer,

try doublequotes

sum({<ddis={">=$(vMinDate)"}>}counter)

Henry

Re: Restricting data based on greater than or equal to date

rubenmarin — Mon, 29 Jun 2015 09:01:06 GMT

Hi Emer, you can create a straight table and create that expression, don't set a caption for that column, in the chart, leave your mouse on the caption (title) of the column to see how QV translates your variable.

Check if vMinDate returns the expected value and it has the same format as ddis. Maybe you only need to enclose one or both values (ddis and vMinDate) in a Date() function.

Or maybe you only need to add an equal sign at the start of the variable expression.

Re: Restricting data based on greater than or equal to date

Anonymous — Mon, 29 Jun 2015 09:12:41 GMT

Hi Emer,

Here's a great tool for getting the Set Analysis syntax right:

http://tools.qlikblog.at/SetAnalysisWizard/QlikView-SetAnalysis_Wizard_and_Generator.aspx?sa=‌

Re: Restricting data based on greater than or equal to date

sgrice — Mon, 29 Jun 2015 09:49:59 GMT

Dates can be unpredictable in qlikview

your statement could have many issues

sum({<ddis={'>=$(vMinDate)'}>}counter)

if vMinDate is not a number but a text string of the date you may need to num it.

if vMinDate is a function like today() then you need to force it to number I.e. $(#=vMinDate)

if ddis is not a real QLikview date but just a string you will need to Date#(ddis,'YYYYMMDD') as ddis in the load script. [replacing YYYYMMDD with the map of the string to date]

I would always use " instead of ' when doing advanced search in set analysis too.

sum({<ddis={">=$(vMinDate)"}>}counter)

Re: Restricting data based on greater than or equal to date

Anonymous — Mon, 29 Jun 2015 10:01:48 GMT

Thanks for your reply, I tried both suggestions and its still not calculating it. When I leave my mouse on the caption it translates vMinDate as a number so ddis mustn't be a number so I will play around a little more to get it right. vMinDate is just selected from a calendar of ddis

Re: Restricting data based on greater than or equal to date

Anonymous — Mon, 29 Jun 2015 10:05:21 GMT

Many thanks, I think it is something about how qlikview is handling the dates, I will make sure that both are in the same format

Re: Restricting data based on greater than or equal to date

Anonymous — Mon, 29 Jun 2015 10:06:28 GMT

Thanks this is a great tool!