Skip to main content
Version Pentaho
Hitachi Vantara Lumada and Pentaho Documentation

Calculated Dates

  1. Last updated
  2. Save as PDF

This task is only useful for result sets that contain date information.

The formula described here will provide a calculated date in a report. Typically you would display a date as a static number or a range, but the process explained below will enable you to display specific dates like "the first Monday of the month" or "every second Wednesday."

  1. Open an existing report or create a new report and establish a data source and query, then drag your data-driven fields onto the canvas.
  2. Select (left click) the text field you want to print the calculated date in; if you do not have a text field dedicated to this task, create one now.
  3. Click the Structure tab and click value in the common section under the Attributes tab.
  4. Click the round green + (Add Expression) icon in the Formula column. The Expression dialogue will appear.
  5. Click the ellipsis (...) to open the Formula Editor dialog box.
  6. Select Date/Time from the Category drop-down box.
  7. Double-click the DATEVALUE item in the list on the left.
  8. Enter in your DATEVALUE formula, then click OK. For more information on DATEVALUE's parameters, see the OASIS reference page for DATEVALUE: http://www.oasis-open.org/committees/download.php/16826/openformula-spec-20060221.html#DATEVALUE and VALUE: http://www.oasis-open.org/committees/download.php/16826/openformula-spec-20060221.html#VALUE. Alternatively you can consult the examples below and modify them for your purposes.
  9. Click Close to exit the Expression dialog box.
  10. Click Preview and verify that your date values are properly calculated and formatted. You may have to adjust your query if it does not produce a testable result set.

The date values you specified should now appear correctly in your report.

Some common calculated date formulas:

1st day of current month
=DATEVALUE(DATE(YEAR(NOW());MONTH(NOW());1))
Sunday of current week
=DATEVALUE(DATE(YEAR(NOW());MONTH(NOW());DAY(NOW())-WEEKDAY(Now();2)))
Saturday of current week
=DATEVALUE(DATE(YEAR(NOW());MONTH(NOW());DAY(NOW())-WEEKDAY(Now())+7))
Current day, date, and time
=NOW()
Current date
=TODAY()
Yesterday's date
=DATEVALUE(DATE(YEAR(NOW());MONTH(NOW());DAY(NOW()-1)))