topic filter function in QlikView

filter function

Fri, 10 Dec 2010 10:02:42 GMT

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

filter function

Miguel_Angel_Baeyens — Fri, 10 Dec 2010 10:16:41 GMT

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.

filter function

Fri, 10 Dec 2010 10:55:52 GMT

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?

filter function

Miguel_Angel_Baeyens — Fri, 10 Dec 2010 11:36:01 GMT

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

filter function

Fri, 10 Dec 2010 11:47:18 GMT

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.

filter function

Miguel_Angel_Baeyens — Fri, 10 Dec 2010 12:08:14 GMT

Hello Rick,

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

Regards.

filter function

Fri, 10 Dec 2010 12:18:07 GMT

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,

filter function

Fri, 10 Dec 2010 13:18:42 GMT

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

filter function

Fri, 10 Dec 2010 13:44:29 GMT

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

hope someone knows a answer:

Regrats

filter function

Miguel_Angel_Baeyens — Fri, 10 Dec 2010 14:07:24 GMT

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

filter function

Fri, 10 Dec 2010 15:39:09 GMT

Miguel,

I know why this doesn't work.
Our companies have at least 2 contracts.
We test your example above it works.
When we add for example company B 2 times (so 2 contracts) bouth will not be showed.

So the 2the or more contracts caught the problem.

Do you know how we can fix this so only the active contracts will be show also when a company has 1 active contract and 1 contract that has end?

filter function

Miguel_Angel_Baeyens — Fri, 10 Dec 2010 18:00:28 GMT


rickvanewijk wrote:Do you know how we can fix this so only the active contracts will be show also when a company has 1 active contract and 1 contract that has end?

Rick,

There may be several solutions:

A) If "Active" is a field that already exists in your database, then use it in the set analysis

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

B) If the "Active" status depends on the end date itself, then something like the following

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

I've used my code above for these new expressions, so you will have to customize them for your needs and field names.

Hope this helps.