Create Calculated Field to Show Difference Between Confirmed Delivery Date and Goods Receipt


Release: 16.11

Module(s): Analytics

Feature ID: ALUSTA-15080

Location: Procurement Custom reporting

Audience: Basware Analytics user

To create a calculated field that shows the average difference between the confirmed delivery date and the first goods receipt or full goods receipt:

  1. From the left drop-down menu, select Procurement and from the right drop-down menu, select Custom.
  2. Click the Edit icon at the bottom right corner of the page.
  3. To create the framework of the report, follow the steps below:
    1. Click the Create new sheet icon located at the bottom left corner of the page.
      As a result, Sheet 2 is created when the report is created for the first time. To rename the sheet, click the secondary mouse button and select the option from the list.
    2. Select the data source marked as PurchaseOrder_PUR which has all the PO line fields and selected PO header fields as well.
    3. Take a look at the following dimensions needed for the calculation that needs to be created.
    4. To create the calculated field, first click the secondary mouse button on the Confirmed delivery date (Order) under Dimensions and select Create calculated field. In the calculated field, write the following formula:
      Formula: IIF([Confirmed delivery date (Order)]<TODAY() AND ISNULL([First goods receipt received time (Order)]),[Order number (Order)],NULL)
      Description of formula: The formula first checks that the confirmed delivery date is earlier than today and that the goods have not been received. If these two conditions are true, the formula returns the order number, otherwise it returns Null.
      Data source: PurchaseOrder
    5. Rename the calculated field as Received after promised delivery date.
    6. Then drag this field to the Filters pane and then exclude all NULL values (refer to #2 in the figure above). Then you can drag the Order Number (Order), Confirmed delivery date, First goods receipt, and a test field called Today_Test_Field which contains the formula as shown in the figure. This will now show you all the order numbers where Confirmed delivery date has passed but no goods receipt has been found.