topic Re: Table Box filters records by selecting date range from two calender objects in QlikView

Table Box filters records by selecting date range from two calender objects

Tue, 18 Feb 2014 15:09:31 GMT

Hello All,

I am workin on a scenario where I have to show my full table,no counts or sum.So I am using simple table box for it.Now I have to put two calender objects(one for selecting start date and other for end date) and by selecting these two dates I have to filter out records in table box.I am done with table box and calender objects.But filteration is not working on selecting dates.Can anyone help me regarding this.The date field in the table was in datetime format,I have convert in into date in qlikview using floor and date function.

Thanks in advance and looking forward for quick reply.

Hey guys,here is the test file.I have added two calender objects and table box ,but data is not filtered on basis of selections of these dates.Please provide me solution for this.I can use straight table chart in place for table box but requirement of showing is just the contents of table ,so no dimensions can be specified.

Message was edited by: Tushar Sharma

Re: Table Box filters records by selecting date range from two calender objects

whiteline — Tue, 18 Feb 2014 19:21:06 GMT

Hi.

Could you post a sample ?

Or describe how the calendar objects are connected with the data model (fields/variables).

Do you have start date and end date as fields in your data model ?

Re: Table Box filters records by selecting date range from two calender objects

Colin-Albert — Tue, 18 Feb 2014 19:31:03 GMT

Use a single list box with a search expression to get the dates between a start and end value rather than two separate list boxes. Enter >01/02/14<17/02/14 as the search expression to select dates from Feb 1st 2014 to Feb 17 2014, if your dates are DD/MM/YYYY format.

Also a table box may not be the best object to use to show your data unless you include a unique ID for each row of data in the table box. A table box object shows each unique row for the selected fields not all rows.

Re: Table Box filters records by selecting date range from two calender objects

maxgro — Tue, 18 Feb 2014 19:48:08 GMT

here you can find a qvw about selecting date range with two calendar

The Basics about Dates, Date Ranges and Set Analysis

Re: Table Box filters records by selecting date range from two calender objects

aveeeeeee7en — Tue, 18 Feb 2014 19:51:40 GMT

Hi Tushar

See the Attached.

Reference:Creating Multiple Search Using a Button

Re: Table Box filters records by selecting date range from two calender objects

Wed, 19 Feb 2014 06:57:30 GMT

Actually,I cant post as it is office work.I create two calender objects.Now for max and min values,I set max and min values of my date field of table for setting max and min values of calender objects.I am not able to connect the data model with calender objects.Data model doesnt have any start date and end date .Data model has one date field,whose max and min values,I need to use as min and max values of my calender objects.

For sample,we can take a table which has two columns(one is unique id and other is ApproveDate.).Now we just need to show this table in qlikview(I am using table box for it,not sure if I am using correct object) and we need to provide a date filter(to choose one start date and other is end date) so that on selecting a date range only those records displayed in the table box which falls in selected range.For creating calender, I want to use datamodel's date column(approvedate).

Re: Table Box filters records by selecting date range from two calender objects

Wed, 19 Feb 2014 07:07:38 GMT

I think,Single list box is not a good option to select two dates.Aslo for the end user,it is not good to enter some expession like this to filter on date.I think,providing two calender objects for selecting start and end date is good option ,also easy to use for the end user.So need to implement this.

My table contains a unique id column,and what object I can use to show simple a table.I didnt want to show any count or sum.Just want to simply show content of my table.

Re: Table Box filters records by selecting date range from two calender objects

whiteline — Wed, 19 Feb 2014 15:29:30 GMT

You have to connect the list boxes to your table.

If you created two variables for start/end you can create a chart instead of the table box and use these variables to show only values between them.

This approach works but it's a step aside from the QV associative logic.

Another way is to create the button that allows to make the corresponding selection in the date field.

More complex approach is to create two triggers for variables to perform the selection each time the values are changed. But it can frustrate the user when he selects the date field directly.

Re: Table Box filters records by selecting date range from two calender objects

Colin-Albert — Wed, 19 Feb 2014 17:08:54 GMT

This example shows how you can select a range of dates

http://community.qlik.com/docs/DOC-5598

Re: Table Box filters records by selecting date range from two calender objects

maxgro — Wed, 19 Feb 2014 20:48:57 GMT

see attachment, I modified your .qwv:

the tablebox is filtered by 2 calendars objects, ecc,ecc,....

the approach is one of the approach suggested by whiteline

".....More complex approach is to create two triggers for variables to perform the selection each time the values are changed......"

but

I fully agree with whiteline, if possible, use qlikview native associative logic

Re: Table Box filters records by selecting date range from two calender objects

Thu, 20 Feb 2014 06:46:41 GMT

The problem with the list box is that,if my date field contains a large number of records then list box will contains a large no of records and it is hard for the user to navigate to particular user.

And with chart,the problem is as I want to show to show only table not any sum or count but in chart it is mandatory to specify some expression.Due to specify expression,straight chart showing extra field which i didnt want.

Also in straight chart doesnt showing horizontal scroll bar with my dimensions,as my table has many columns ther are not fitted within the chart.

In table box ,I can achieve all this but i am not able to filter on calender selection in table box.

Creating button and creating triggers for variables is looking good to me.I have already tried these two ways, but some how they are not working on table box.

If possible,could you please modify my qvw file.Thanks

Re: Table Box filters records by selecting date range from two calender objects

Colin-Albert — Thu, 20 Feb 2014 07:50:41 GMT

You can add a dummy expression of 1 to the straight table and hide the column on the presentation tab.

Re: Table Box filters records by selecting date range from two calender objects

Thu, 20 Feb 2014 08:10:34 GMT

Thanks for providing solution.But this os failed when I select a date from start date(suppose 20feb 2014) and selects end date(suppose 20jan 2014).In this case it is not showing corect result.

Hey guys I am lmost there.I implement the changes which are suggested by all of you.But not achieving what I want.Please help me regarding this.

Re: Table Box filters records by selecting date range from two calender objects

Thu, 20 Feb 2014 08:21:05 GMT

Smae problem with the solution,as if we selects greater date in start date and less date in end date calender,then it is not shoiwn the correct data.I am working on it,as I change max value in start date calender to vend variable .Also change the min value in end date calender to vstart.I am testing it.But I think it is not a good solution as it may be frustrating for user. Do any one have better solution,so that we can restrict user to selects always a less date in start date in start date calender .Quick response expected.Thank you.l

Re: Table Box filters records by selecting date range from two calender objects

Thu, 20 Feb 2014 11:21:21 GMT

Ohk thanks,but about Horizontal scrool bar and is it not possible to work it out in table box.I preferably want to use table box.

Re: Re: Table Box filters records by selecting date range from two calender objects

aveeeeeee7en — Thu, 20 Feb 2014 12:10:21 GMT

Dear Tushar

Please see the Attached. I have reworked on the Solution.

May this will fix your issue.

Regards

Aviral Nag

Re: Table Box filters records by selecting date range from two calender objects

Thu, 20 Feb 2014 12:27:11 GMT

Thanks for your reply.But I am not usre why I am not able to implement your solution.Even your first solution is good in which you use button.I have decided to implement button but sont know why it is not working.If possible ,please change in my qvw.That will be very helpful to me.