5 Replies Latest reply on Jun 7, 2017 7:11 PM by lei.chen.0

    How to obtain the last record (date type) and not categorize the Calc Field as "Agregate"

    Thiago Castro

      Hi,

       

      I need to create a report that gives me a KPI of the percentage of Deliveries on Time according to a certain period.

       

      DIFOT – Delivery In Full On Time

          

      Sales Order

      Sales Order Item

      Planned Delivery Date

      Real Delivery Date

      (Invoice)

      DIFOT

      50001

      1

      01/25/2017

      01/25/2017

      50%

      50001

      2

      01/25/2017

      01/27/2017

      50001

      3

      01/30/2017

      01/30/2017

      100%

       

       

      DIFOT for S.O. 50001 should be 75%

      The Report takes in consideration the lines with same Planned Delivery Date as one consolidated delivery and ONLY Sales Order completely delivered (balance or pending deliveries are zero).

      In case there are partial deliveries for an Item, the report should take in consideration the last invoicing date for that particular item.

       

       

      Real Tableau example:

      In this example, I’m using data from 2 tables:

      NF_Item (Invoice)

      PV_Item (Sales Order)

       

      In order to calculate if the product was delivered in full on time, I had to create a Calculated Field to determine the last invoiced date for a particular S.O. Item.:

      Last Invoice

      (this is required to get the date when the last the last invoice was generated to the specific Sales, avoiding parcial invoicing)

      When trying to create an additional Calculated Field for the DIFOT, I got an inconsistency:

       

      DIFOT

      (calculation that gives me 1 when the Sales was invoiced before the planned date and 0 when the Sales was invoiced after the planned date)

       

      Picture1.png

       

      PROBLEM: As calculated fieldLast Invoiceis an agregation, it cannot be used to compare with a non-agregation field.

      How to fix this issue? Or how to obtain the last invoiced date not as Agregation type?

       

      Thank you,

      Thiago