filter function

[#1]

Hello,

I have a problem i can't solve.

I made a tabelobject what show the following information:
- company name
- contract_startdate
- contract_enddate

The function of the tabel is to show when a compony started there contract end when it ends.

My question is: is it possible to create a function where i can put a specifik date, where the tabel reacts by showing only company names were contract_enddate is higher then <inputdate>.

I hope someone can help me?

Regrats,
Rick

[Miguel_Angel_Baeyens]

Hello Rick,

First, create a variable vDate in the menu Settings, Variable Overview.

Then, create a slider/calendar object and set it to use variable vDate. Set the minimum to

=Min(contract_enddate)

and the maximum to

=Max(contract_enddate)

Go to the Number tab and set it to "Date"

Now select any date in this object you have just created and check that the date is displayed correctly.

If so, create a new chart, pivot table, add Company name as dimension and create two expressions:

Only({< contract_enddate = {'>$(=Date(vDate))'} >} contract_startdate)

Only({< contract_enddate = {'>$(=Date(vDate))'} >} contract_enddate)

Now you should see only those companies and dates greater than the date you have selected in your calendar object. If you change the object, the chart will be changed dynamically.

Hope this helps.

[#3]

Miguel,

Thanks for your fast reply.

I've created a working slider/calender.

But when i create the pivot table it doesnt show any results.

any idea?

[Miguel_Angel_Baeyens]

Is the date shown dates correctly? What dimension and expressions do you have in your chart?

[#5]

the date shown dates correctly, The vDate veriable also change when i change date so i geuss it works.

I use Company name as dimension, with no start or end date.
and i use the expressions as you mention with a little change to make the names correct.

[Miguel_Angel_Baeyens]

Hello Rick,

You may post some sample data so we can draw a chart based on it.

Regards.

[#7]

Miguel,

I followed exact the first instruction you've posted but doenst work.
I don't understand what you mean with sample data.
What kind of information do you mean.

Regards,

[#8]

Ensure that contract_enddate is recognized as a Date and not as a string.
If not the solution suggest above will not work.

[#9]

al fields are date fields,
also didn't fix it.

hope someone knows a answer:

Regrats

[Miguel_Angel_Baeyens]

Rick,

1.- I'm using this data (script):

Data:LOAD * INLINE [CompanyName, StartDate, EndDateA, 01/01/2010, 01/05/2010B, 01/02/2010, 20/02/2010C, 01/02/2010, 01/03/2010D, 01/04/2010, 30/04/2010];

2.- Create a variable named vDate

3.- Create a Slider/Calendar object using variable vDate, set minimum and maximum date as above.

4.- Create a new chart, Pivot Table, select CompanyName as Dimension

5.- Use the following as expression Start Date

Only({< EndDate = {'>$(=Date(vDate))'} >} StartDate)

6.- Use the following as expression End Date

Only({< EndDate = {'>$(=Date(vDate))'} >} EndDate)

Close the chart properties.

Now select in your calendar a date. Select March 31, 2010. According to your description, it will show two results (the two companies which end date is greater than 31/03/2010): A and D, with their corresponding start and end dates.

Hope this helps