You should now be presented with the following prompt:Įnter a value.

Rebuild your report and pull your new object User_Prompted_Days into your query alongside your date object. Give it a meaningful name such as number of days to add/subtract:’) However, in order to do this, you need to add a new object to your Business Objects universe.Ĭreate a new dummy object in the Business Objects universe which holds a User Response prompt. What if I want to prompt the user for a value to use in this formula? This means that the user will be prompted for the number of days to add or subtract from 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 subtracts seven days to the value held in the Date Field. Remember that this should be a negative value. This adds seven days to the value held in the Date Field Your date object may have a different name.Īdd a new column to the right of the Date Field column, and build the following formula: In this case I am using an object name called ‘Date Field’. If you want to subtract a number of days, ensure that the value you enter is a negative number (proceeded by a minus sign).Ĭreate your report and pull in a date field.

Note that the second value should be a numeric value and should not be in single or double quotes. These two values are separated by a semi colon in WebIntelligence. The second value is the number of days you want to add or subtract. The first is the date field you wish to add/subtract from. WebIntelligence provides a function to allow us to do this: The credit control team may want to identify those invoices which have exceeded those 30 days terms. 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. This may seem illogical at first, but the structure of the modern calendar means that there is no perfect way of calculating dates including months and years.įor calculations with several possible results, the calculator shows the Alternative result below the primary result.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. If it added the days first, the result would be April 1 (February 28 + 1 day = March 1 March 1 + 1 month = April 1). The reason is that the calculator first adds 1 month to jump from February 28 to March 28, then it adds one day to arrive at March 29. However, if you add one month and one day to February 28, which in a common year is the day before March 1, the result is March 29, which is 4 days before April 2. In some cases, this method can lead to unexpected results.įor example, if you add one month and one day to March 1, the result is April 2, as is to be expected (March 1 + 1 month = April 1 April 1 + 1 day = April 2). Although the total number of days in January (31) and February (28) are different both are counted as 1 month. This means that it adds years, then months, then days. The calculator adds or subtracts the larger time units before the smaller time units. Business Date to Date (exclude holidays).

Calculate date by subtracting days