Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Filter Table by Expression: Last 7 Days
Filter Table by Expression: Last 7 Days [message #1064096] Mon, 17 June 2013 11:41 Go to next message
Eric Muehlstein is currently offline Eric Muehlstein
Messages: 6
Registered: June 2013
Junior Member
I'd like to filter a table where 'ClosedDate' occurred in the previous 7 days or 'Status' != 'Closed'. Following the guidance of another forum post I created a filter where the following expression is equal to 1:

if( (row["ClosedDate"] > DateTimeSpan.subDate(new Date(),0,0,7)) || ( row["Status"] != 'Closed' )) {
1;
}else{
0;
}

I'm catching the != 'Closed' rows but not the 'Closed' rows where the closure date was in the last 7 days.

Any ideas?

Thanks
Re: Filter Table by Expression: Last 7 Days [message #1064138 is a reply to message #1064096] Mon, 17 June 2013 14:48 Go to previous messageGo to next message
Kristopher Clark is currently offline Kristopher Clark
Messages: 130
Registered: January 2013
Senior Member
Can you give me some example data showing how row["ClosedDate"] is formatted vs how DateTimeSpan.subDate(new Date(),0,0,7) is formatted?
Re: Filter Table by Expression: Last 7 Days [message #1064141 is a reply to message #1064138] Mon, 17 June 2013 14:55 Go to previous messageGo to next message
Eric Muehlstein is currently offline Eric Muehlstein
Messages: 6
Registered: June 2013
Junior Member
Added a computed column named Test using the expression 'DateTimeSpan.subDate(new Date(),0,0,7)'

The results are below:

ClosedTime Test
Dec 31, 1969 7:00 PM Jun 10, 2013 1:50 PM
Jun 14, 2013 5:58 AM Jun 10, 2013 1:50 PM
Dec 31, 1969 7:00 PM Jun 10, 2013 1:50 PM
Dec 31, 1969 7:00 PM Jun 10, 2013 1:50 PM
Jun 17, 2013 9:21 AM Jun 10, 2013 1:50 PM

Re: Filter Table by Expression: Last 7 Days [message #1064144 is a reply to message #1064141] Mon, 17 June 2013 15:27 Go to previous messageGo to next message
Eric Muehlstein is currently offline Eric Muehlstein
Messages: 6
Registered: June 2013
Junior Member
Furthermore, adding a dynamic text field in the table with the complete expression returns 1 or 0 as expected:

Closed Jun 14, 2013 5:58 AM Jun 10, 2013 2:26 PM 1
Open Dec 31, 1969 7:00 PM Jun 10, 2013 2:26 PM 1
Open Dec 31, 1969 7:00 PM Jun 10, 2013 2:26 PM 1
Closed Jun 17, 2013 9:21 AM Jun 10, 2013 2:26 PM 1
Re: Filter Table by Expression: Last 7 Days [message #1064145 is a reply to message #1064144] Mon, 17 June 2013 15:52 Go to previous message
Eric Muehlstein is currently offline Eric Muehlstein
Messages: 6
Registered: June 2013
Junior Member
I've settled on a workaround. I created a new computed column called 'DaysSinceClose' equal to:

BirtDateTime.diffDay( row["ClosedDate"], BirtDateTime.now() )

and filtered the table with the follow expression equal to 1:

if( (row["DaysSinceClose"] <= 7) || ( row["Status"] != 'Closed' )) {
1;
}else{
0;
}
Previous Topic:Parameterized Query - Problem
Next Topic:Problem with type "date"
Goto Forum:
  


Current Time: Tue Jul 29 19:05:08 EDT 2014

Powered by FUDForum. Page generated in 0.01709 seconds