Filtering problem using from date and to date

Filtering problem using from date and to date

  

Hi All,

I am trying to filter search results using date range known as from date and to date. I have 10 records in Tracker but it's not shown because of the filter I added,

Tracker.Date >= Session.TrackersFromDate and Tracker.Date <= Session.TrackersToDate

Can someone suggest me what to do?Thanks in advance

Hi,


Depends how to complete aggregate looks like and what your test-values are (and possible test-data)


Hi, verify that the parameters are of type Date or DateTime instead of text. This can be a problem.

J. wrote:

Hi,


Depends how to complete aggregate looks like and what your test-values are (and possible test-data)


Hi,

The real problem is if I don't select from date and to date the results aren't shown because in filter the default values of text is " " whereas for date is nulldate() So it automatically filters date with nulldate values which aren't available.


Alexandre Costa wrote:

Hi, verify that the parameters are of type Date or DateTime instead of text. This can be a problem.

Hi,

The parameters are of type date only.I have defined about my problem above as reply to J. Please check it


Hi,


please provide a complete example (oml by preference), otherwise it's simply guessing if you made a typo or not.


Solution

Ooook.

I think I got it.
Your filter is FORCING you to provide the values, or it found nothing and no results returned.

This is because you need to have a "switch" to turn on/off the filters. Separate these two conditions in two different filters:

 

Session.TrackersFromDate = NullDate() OR SessionTracker.Date >= Session.TrackersFromDate 
Session.TrackersToDate = NullDate() OR SessionTracker.Date <= Session.TrackersToDate

Cheers,
Eduardo Jauch



Solution

ragul sp wrote:

J. wrote:

Hi,


Depends how to complete aggregate looks like and what your test-values are (and possible test-data)


Hi,

The real problem is if I don't select from date and to date the results aren't shown because in filter the default values of text is " " whereas for date is nulldate() So it automatically filters date with nulldate values which aren't available.



Hi Ragul,

I have faced the same problem before.

If you want to get data when you dont put any from date and to date there, you need to put Null Value on the input widget.

Do not put the default value on the StartDate variable unless you won't get the result that you want.


In this way, you still can use a single row filter only:

Tracker.Date >= Session.TrackersFromDate and Tracker.Date <= Session.TrackersToDate

Cheers,

Lady

Lady wrote:

ragul sp wrote:

J. wrote:

Hi,


Depends how to complete aggregate looks like and what your test-values are (and possible test-data)


Hi,

The real problem is if I don't select from date and to date the results aren't shown because in filter the default values of text is " " whereas for date is nulldate() So it automatically filters date with nulldate values which aren't available.



Hi Ragul,

I have faced the same problem before.

If you want to get data when you dont put any from date and to date there, you need to put Null Value on the input widget.

Do not put the default value on the StartDate variable unless you won't get the result that you want.


In this way, you still can use a single row filter only:

Tracker.Date >= Session.TrackersFromDate and Tracker.Date <= Session.TrackersToDate

Cheers,

Lady

Hello Lady,

The special value is required. But this is not enough.

While in the case session variable is NullDate, the first part will mostly work, because the date will probably be also NullDate or grater than it, the second filter will fail, because unless date is also NullDate, the comparison will always return false, because date will likely to be always greater than NullDate.

Cheers,
Eduardo Jauch

Eduardo Jauch wrote:

Lady wrote:

ragul sp wrote:

J. wrote:

Hi,


Depends how to complete aggregate looks like and what your test-values are (and possible test-data)


Hi,

The real problem is if I don't select from date and to date the results aren't shown because in filter the default values of text is " " whereas for date is nulldate() So it automatically filters date with nulldate values which aren't available.



Hi Ragul,

I have faced the same problem before.

If you want to get data when you dont put any from date and to date there, you need to put Null Value on the input widget.

Do not put the default value on the StartDate variable unless you won't get the result that you want.


In this way, you still can use a single row filter only:

Tracker.Date >= Session.TrackersFromDate and Tracker.Date <= Session.TrackersToDate

Cheers,

Lady

Hello Lady,

The special value is required. But this is not enough.

While in the case session variable is NullDate, the first part will mostly work, because the date will probably be also NullDate or grater than it, the second filter will fail, because unless date is also NullDate, the comparison will always return false, because date will likely to be always greater than NullDate.

Cheers,
Eduardo Jauch


Hhhmmm,


I always using this way and never got any issue.

Is it magic? :D


Cheers~


Eduardo Jauch wrote:

Ooook.

I think I got it.
Your filter is FORCING you to provide the values, or it found nothing and no results returned.

This is because you need to have a "switch" to turn on/off the filters. Separate these two conditions in two different filters:

 

Session.TrackersFromDate = NullDate() OR SessionTracker.Date >= Session.TrackersFromDate 
Session.TrackersToDate = NullDate() OR SessionTracker.Date <= Session.TrackersToDate

Cheers,
Eduardo Jauch



Hi,

You got it right.It worked without any difficulties.Thank you so much.You have helped me earn up my lost time.

Lady wrote:

Hhhmmm,


I always using this way and never got any issue.

Is it magic? :D


Cheers~


lol.

Don't know.
I suppose I can test, but the simple filter bellow will not fetch any information if the Session variable is NullDate.

Tracker.Date >= Session.TrackersFromDate and Tracker.Date <= Session.TrackersToDate

And this is easy to understand with an example.

Two records, with the following dates "2018-01-01" and "2018-01-05".
Session variable set to NullDate. ("1900-01-01")

The result will be the same for both dates:

The first comparison returns True, as Date is in fact higher than the session value.
The second comparison returns False, as Date is, in fact, higher than the session value.

The result is than TRUE and FALSE, that is FALSE.
So, both records will be removed from the list, when you wanted them in.

I don't see how this can work... Ô.ó

Cheers.

ragul sp wrote:

Hi,

You got it right.It worked without any difficulties.Thank you so much.You have helped me earn up my lost time.

Glad I was of help :)