cancel
Showing results for 
Search instead for 
Did you mean: 

Filter Date field based on Formula

former_member225386
Participant
0 Kudos

Hi All,

Could anybody help me to filter the date field based on the below requirement,

1)User will select the End date and number of weeks like 26,52,2 year and 5 years:

Based on the above selection I need to calculate the start date and need to filter the date field based on Start date and End date.

Previously I used Parameter field to get both start date and end date then used the parameter field in report filter to filter the date field.

Accepted Solutions (1)

Accepted Solutions (1)

former_member225386
Participant
0 Kudos

Hi Raghav/Abhilash,

Thank you!

But the DateAdd('ww',-52,{?Date_Prompt}) returning date field requied error.

End_Date is in Date format and Week is in number format.

Please help me on this.

raghavendra_hullur
Active Contributor
0 Kudos

Hi,

Is your date prompt in date datatype or date time type?

If it's of date time type, use CDate on top of DateAdd function.

Use below code:

cDate(dateAdd('ww', -{?weeks}, {?Date}))

Thanks,

Raghavendra

Answers (1)

Answers (1)

raghavendra_hullur
Active Contributor
0 Kudos

Hi,

Can you explain more on the number values entered by users?

Is it like number of weeks or years provided by user for start date needs to be the value prior to the value entered for end date?

Also, are the parameters part of command level or created at report level?

Thanks,

Raghavendra

former_member225386
Participant
0 Kudos

Hi Raghav,

For example:

User is selecting End date and weeks as: 10/11/2014 and 52(weeks)

Then the Start date need to be: 09/11/2013

The End Date parameter is created in report level.


End date: Calendar Date

Weeks: 26 or 52 or 104 or 260

raghavendra_hullur
Active Contributor
0 Kudos

Hi,

You can try creating a formula with below content and use it for start date.

datevar date1;

dateVar temp1:= dateAdd('ww', -<week interval>, <end date>);

date1 = dateAdd('d', -2, temp1);

date1


Replace <week interval> with your Week parameter and  <end date> with your end date parameter.

- needs to be retained before week parameter


Thanks,

Raghavendra

raghavendra_hullur
Active Contributor
0 Kudos

Hi small change is required in the formula as the final output will be a date time value.

So, please use below one:

datevar date1;

dateVar temp1:= dateAdd('ww', -<week interval>, <end date>);

cDate(dateAdd('d', -2, temp1));

Thanks,

Raghavendra

abhilash_kumar
Active Contributor
0 Kudos

Hi Ramesh,

How did you arrive at '09/11/2013' as the Start Date when going back 52 weeks?

DateVar StartDate := DateAdd('ww',-52,{?Date_Prompt});

{Database_Field} IN [StartDate TO {?Date_Prompt}];

-Abhilash