How to add or subtract a number of days from a date field in Web Intelligence

A report developer may be required to display a date field in a report which is made up of an existing date field plus/minus x number of days. A good example of this may be a report developed for the credit control function where you have issued an invoice on 30 day terms. The credit control team may want to identify those invoices which have exceeded those 30 days terms.

WebIntelligence provides a function to allow us to do this:

RelativeDate()

This function requires two values.

The first is the date field you wish to add/subtract from. The second value is the number of days you want to add or subtract. These two values are separated by a semi colon in WebIntelligence.

Note that the second value should be a numeric value and should not be in single or double quotes.

If you want to subtract a number of days, ensure that the value you enter is a negative number (proceeded by a minus sign).

Lets work through a couple of examples:

Adding seven days to a date field

Create your report and pull in a date field.

In this case I am using an object name called ‘Date Field’. Your date object may have a different name.

Add a new column to the right of the Date Field column, and build the following formula:

=RelativeDate([Date Field];)

*Replace the <number of days to add> with your value

=RelativeDate([Date Field];7)

This adds seven days to the value held in the Date Field

Subtracting seven days from a date field

Create your report and pull in a date field.

Add a new column to the right of the Date Field column, and build the following formula:

=RelativeDate([Date Field];-)

*Replace the <number of days to subtract> with your value. Remember that this should be a negative value.

=RelativeDate([Date Field];-7)

This subtracts seven days to the value held in the Date Field.

If you have access to Universe Designer, or can request a new object to be added to your reporting universe, we can take this to another level by making that value a dynamic field. This means that the user will be prompted for the number of days to add or subtract from the Date Field.

What if I want to prompt the user for a value to use in this formula?

This is possible. However, in order to do this, you need to add a new object to your Business Objects universe.

Create a new dummy object in the Business Objects universe which holds a User Response prompt.

Give it a meaningful name such as User_Prompted_Days.

@Prompt(‘Enter number of days to add/subtract:’)

Save and export the universe.

Rebuild your report and pull your new object User_Prompted_Days into your query alongside your date object.

Refresh the report.

You should now be presented with the following prompt:

“Enter number of days to add/subtract:”

Enter a value. This value will get stored in the object called User_Prompted_Days.

We can now use this object to build up our new date column.

Add a new column, and populate this column with the following formula:

=RelativeDate([Date Field];ToNumber([User_Prompted_Days]))

We use ToNumber in this formula as the prompt value will be stored as a character string.

You should end up with a new date based on the value held in the Date Field column plus/minus the days entered by the user at the prompt.

This approach has a benefit in that the value entered by the user can be applied to any number of date fields you bring back in your report.

If you find that you only need to apply the user response to a particular date field, consider creating an object with the prompt built in:

The object code to subtract x days from the current date in SQL Server is:

convert(datetime,convert( varchar,dateadd(dd,-@Prompt(‘Number of days to subtract from current date:’,’N’,,MONO,FREE),getdate()),102))

The object code to add x days to the current date in SQL Server is:

convert(datetime,convert( varchar,dateadd(dd,@Prompt(‘Number of days to subtract from current date:’,’N’,,MONO,FREE),getdate()),102))

In case you missed it, the key difference in the two formulas is the minus sign before the @Prompt.

Hopefully this will have helped you understand how to add or remove days from a date field in Web Intelligence.

About these ads

3 Responses to “How to add or subtract a number of days from a date field in Web Intelligence”

  1. krish Says:

    can we know how to do this for a previous month and previous year

  2. Miles Says:

    Thank you- this is really great information. Could you please elaborate on the last part of your example at top – “identify those invoices which have exceeded…..” I am trying to create a report that returns all data records where a certain date/time object is equal to or greater than 120 hours “old,” or 5 days. In other words, if this date/time passed 120 hours or more ago, give me those results in my report. If this date/time passed only 110 hours ago – do not show in my results. Using your instructions above I can easily add 5 days to the date/time object but I can’t figure what then to do with that.

    • weldblog Says:

      Hi Miles

      This should be possible using variables and a filter.

      The first step is to create a variable with your calculated date formula. Give that variable a name, e.g. v_CalculatedDateField.

      Then create a filter on your report to show only those dates (use the date field brought in by the query) that are greater than five days (based on your new variable).

      Something like PurchaseDate >= v_CalculatedDateField.

      Let me know if this works. If not, let me know whether you are using WebIntelligence or full client Business Objects and the name of your objects, and I will give you the exact formula.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 105 other followers

%d bloggers like this: