how to set date range

[#1]

hi friends,

I have data source with project's data. There are some database tables contain following attributes:

[name of project], [name of customer], [project manager], ..., [project start], [project end], ...

and naturally, each project can take more than one month, eg. from 1.1.2010 to 31.5.2010, or from 1.9.2009 to 31.8.2010.

In my project in QlikView I would want to have ListBox with list of Year and other ListBox with list of Month.

How can I construct loading data script and how can I set QlikView sheet objects for if I want select:

- for "Year" 2010, choose "Month" (for example - February) and I want displayed all projects have ([project start]<=1.2.2010 AND [project end]>1.2.2010) OR ([project start]>=1.2.2010 AND [project start]<=28.2.2010)

- for "Year" 2010 I want displayed all projects have ([project start]<=1.1.2010 AND [project end]>=1.1.2010) OR ([project start]>=1.1.2010 AND [project start]<=31.12.2010)

very thanks for tip

[#2]

for more detailed info:

for selection Year=2010, Month=February -> dislayed all project which take through February 2010, or started at Fenruary 2010 or ended at February 2010.

for selection Year=2010 Month=all -> displayed all project which take through 2010, or started at 2010 (Jan to Dec), or ended at 2010 (from Jan to Dec)

[#3]

When you make a selection in Month or Year, you can use Only(Month) and Only(Year) to return the selection (there are other ways, but this should work for you here).

So, if you have [project start] and [project end] an expression to give you any project that starts before the selected month and end within the selected month would be something like:

Sum({<[project start] = {'<=$(=MakeDate(Only(Year), Only(Month))'},
 [project end] = {'>$(=MakeDate(Only(Year), Only(Month))'}>} Value)

You need an aggregate function to use Set Analysis, so I used Sum in my example. You should be able to convert most expressions to an aggregate to use this Set Analysis. Your other expressions should use similar syntax to the one above.

EDIT: I forgot to mention that this example would depend on a separate calendar table that would not be connected to project start or project end. You would need a Month field populated with 1-12 and a Year field containing any year you would like to evaluate on. This could be handled easily with Inline loads.

[#4]

hi NMiller,

my [project start] attribute is in format "DD.MM.YYYY" and if I create ListBox with field expression:

=Only([project start])

nothing is displayed. The same situation is with field expression:

=Only(Month([project start]))

nothing is displayed.

[#5]

Yes, Only() is a function that returns a value when there is one and only one value for the expression. If there is more than one value (in your case), then it returns null. If you make a selection on project start, say 1.2.2010 and then do only([project start]) in a text box or chart expression, then you will get 1.2.2010.

The Only() function works in the Set Analysis portion of the expression I gave because it will evaluate based on the selection. It doesn't really make sense to use it inside of a List Box, because List Boxes are used to select from multiple choices whereas the Only() function will at best return only one value. Usually, when using Only you are looking for a single selection in a field (although there are plenty of other uses as well).