topic Re: Condition based on today's date? in QlikView

Condition based on today's date?

Tue, 20 Nov 2012 23:25:11 GMT

Hi All,

I am trying to restrict a table to show only rows of data where a date column (let's call it EndDate) is greater or equal to today (the day when the document is viewed or at least when it was last updated works for me).

I didn't seem to get a condition in so I tried to create a new variable that compares EndDate from my data source to today's date and returns NULL if it is outside that range. The functions people talk about in these forums are today() and now() but neither really seems to work.

How can I add such a column to my data? In the script I tried somthing like this:

if([EndDate] >= today() and [EndDate] <= today()+30, [EndDate], NULL() ) as DuringNext30

The example above is something I imagined working but it didn't.

even something like:

today() as TodaysDate

didn't work. I guess I am trying some functions that may have a different name I don't know?

Thank You for your help!

Marco

Re: Condition based on today's date?

Wed, 21 Nov 2012 00:02:55 GMT

In particular I want to show 3 different tables that do not analyze or calculate anything.

I want to show

one table with EndDate >= today and EndDate <= today+30

one table with EndDate > today+30

one table with EndDate < today

so I don't want to alter the dataset for the whole document, just for the table objects.

This really feels like such a rookie question but I am stuck. Just starting out with QlikView.

Marco

Re: Condition based on today's date?

Wed, 21 Nov 2012 05:29:20 GMT

First check the format of the enddate and today() are same are not

if both are same format then use the cond

otherwise use the conversion functions like date,timestamp

in ur exp instead of today()+30 use below expr

=date(today()+30)

if([EndDate] >= date(today()) and [EndDate] <= date(today()+30), [EndDate], NULL() )

Re: Condition based on today's date?

Wed, 21 Nov 2012 14:20:47 GMT

Hm, I get an error telling me I have invalid data near the if statement.

Before QlikView I used iNetSoft and there I could simply add any condition to any sheet object. I could right-clik on it and select condition and then say: Of all the lines of data, use only those where EndDate >= today. This was based on the object, not the whole sheet, so I could use different conditions for each table and chart.

This is what I want to do here. I'm sure it's possible and probably the solution is better than the one in iNetSoft. However, I just can't find where I can set this up.

Re: Condition based on today's date?

Wed, 21 Nov 2012 19:11:17 GMT

I think I figured out what happened:

I added a table as a new sheet object. Here it didn't work. What I have to do is to add a chart and set the chart type to straight table - then it works.

Not a very intuitive way to do it, I have to say.

Marco