Combine 'Only' Function

[#1]

Hi all,

Let me start by thankin you all for this great forum, I fixed a lot of my 'problems' by reading your posts.

I understand how the Only function works when using one condition, but I'm having some problems combining conditions (don't even know if this is possible at all).

We've got a database which has some dates in it. Now I'm trying to filter these dates. For example; I only want to show the employees who have been active in February:

1. I'm using the folowing data:

LOAD * INLINE
[Employee, Start_date, End_date
1, 2-2-2010, 5-2-2010
2, 1-1-2010, 5-2-2010
3, 1-1-2010, 5-3-2010
4, 2-2-2010, 5-3-2010
5, 1-1-2010, 15-1-2010
6, 5-3-2010, 16-3-2010
7, 1-1-2010, ,]

2. I've made two variables (Start and End) and made a input box with these variables. Startdate = 1-2-2010 and Enddate = 28-2-2010

3. Created a pivot table with 'Employee' as Dimension

4. Created the folowing expressions:

Only({< Start_date = {'<=$(=Date(End))'}, End_date = {'>=$(=Date(Start))'} >} Start_date)

AS Start

Only({< Start_date = {'<=$(=Date(End))'}, End_date = {'>=$(=Date(Start))'} >} End_date)

AS End

Now I only get Employee 1,2,3 and 4 as a result. But number 7 was also active in the periode. I probably have to do edit my expressions with 'or End_date = null' or something like that, but I'm not sure.

Hope you guys can help me.

TIA,
Fred

[#2]

Only just needs to have one possible value to return. In this case, you should be able to use:

Only({<Start_date={'>=$(vStartDate)<=$(vEndDate)'}> +
 <End_date={'>=$(vStartDate)<=$(vEndDate)'}> +
 <Start_date={'<=$(vStartDate)'},
 End_date={'>=$(vEndDate)'}>}
Start_date)

Only works, because there is one value of Start_date for every user, which is the dimension in your table. I posted a sample file. If you were to have more than one value, Max may be better. In order to pull the users that match the criteria, you only need the expression to return a value. Only will sometimes return null even if the criteria is met (when there is more than one value), so it is not always the best choice.

[#3]

Hi NMiller,

Thanks for your reply. I edited my post yesterday around the same time you replied.
I figured out the variables I have to use:

Only({< Start_date = {'<=$(End)'}, End_date = {'>=$(Start)'} >} Start_date)

Only({< Start_date = {'<=$(End)'}, End_date = {'>=$(Start)'} >} End_date)

But what if an employee doesn't have an end_date, which means he/she is still active? If I use my variables I don't get them as a result.

So 'Only' only gives me one result... It's also possible that an employee has had more contracts in the same period, for example:
Employee Start_date End_date
1 1-2-2010 5-2-2010
1 15-2-2010 13-3-2010

I would like to get them both as a result. Is there another function I can use (similiar to the 'where' command in SQL)?

[#4 · Accepted solution]

I'm not sure how you can get the answer with only two. You need:

1. Started in Feb
2. Ended in Feb
3. Started before Feb and ended after Feb (or didn't end, per your new requirement)

To get multiple records per employee, you'd need to add Start_date as a dimension on your chart. Then use something like:

Only({<Start_date={'>=$(vStartDate)<=$(vEndDate)'}> +
 <End_date={'>=$(vStartDate)<=$(vEndDate)'}> +
 <Start_date={'<=$(vStartDate)'},
 End_date={'>=$(vEndDate)'}>
}
End_date)

That way, if the record doesn't match your criteria, the entire record will be suppressed. If multiple Start_dates for the employee match the criteria, they will be displayed.

The null End_date is going to give you troubles, since you really can't require a value be null using Set Analysis. I would replace the null End_dates with a date significanlty far into the future or something like the dash character to help with the calculations.

[#5]

Thanks!