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

# Apply rule based calculation on table data

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 Data Row level algorithm Unit ID Status Service Request ID Service Request Date Assignment Planned Assignment Plan Date Assignment Done Actual Service Date Required Service Deployment Date (A) Actual/Expected Service Deployment Date (B) Delay in days(B-A)>0 Per Day Cost Delay Cost Logic Kolkata CLOSED 1 01/02/18 Yes 20/02/18 Yes 02/03/18 21/02/18 02/03/18 10 1000 10000 For closed job,- A = Service Request date + 20- B = Actual Service Date Mumbai OPEN 2 11/02/18 Yes 20/02/18 No 03/03/18 02/01/19 306 1000 306000 For Open job- A = Service Request date + 20- B = Today() Delhi Ext Service Sought 3 20/03/18 Yes 15/08/18 Yes 26/04/18 29/04/18 26/04/18 0 1000 0 For External service case- A = Service Request date + 40- B = Actual Service DateHowever, as service is done withing timeline (B-A<0), hence Delay to be considered zero
• ###### 1. Re: Apply rule based calculation on table data

If every row has the (A) an (B) dates, then yes, you can make asimple calc to do DATEDIFF on those two dates.

And if there is no (B) date, then you can use TODAY() in place of the (B) date.

And if there is no (A) date, then there is no calculation to be done.  Delay defaults to 0.