1 Reply Latest reply on Jan 2, 2019 12:07 PM by Joe Oppelt

    Apply rule based calculation on table data

    Prem Nath

      Hi All,

       

      I am struggling to apply rule based calculation using Tableau.

       

      Here is the scenario:

       

      This data set is related to service delivery - once a service request is raised, concerned team takes action and ensures delivery - planned date and actual delivery date are captured for each activity.

       

      ID Status : Every service request may have status such as "Closed", 'Open' or "Ext Service Sought"

      Assignment Plan Date : Shows the date when assignment in planned by Planning team

      Actual Service Date : This is the date when actual service delivery is done

       

      Delay is calculated based on Actual or Expected Delivery date (B) and Required Service Deployment date (A). Both A and B has some rule - as captured in "Delay Cost Logic" column

       

       

      I don't whether this is possible on Tableau (I hope it is!) or need to use any other data analysis software such as R, Python etc.

       

       

       

      Available DataRow level algorithm
      UnitID StatusService Request IDService Request DateAssignment PlannedAssignment Plan DateAssignment DoneActual Service DateRequired Service Deployment Date (A)Actual/Expected Service Deployment Date (B)Delay in days
      (B-A)>0
      Per Day CostDelay CostLogic
      KolkataCLOSED101/02/18Yes20/02/18Yes02/03/1821/02/1802/03/1810100010000For closed job,
      - A = Service Request date + 20
      - B = Actual Service Date
      MumbaiOPEN211/02/18Yes20/02/18No03/03/1802/01/193061000306000For Open job
      - A = Service Request date + 20
      - B = Today()
      DelhiExt Service Sought320/03/18Yes15/08/18Yes26/04/1829/04/1826/04/18010000For External service case
      - A = Service Request date + 40
      - B = Actual Service Date
      However, as service is done withing timeline (B-A<0), hence Delay to be considered zero