1 Reply Latest reply on Apr 9, 2018 1:47 PM by Eric Hammond

    Passing Dimension Values to a Calculated Field

    Richard Couillard

      Hi. I need to calculate the number of days between two dates (for SLA calculations) based on the following criteria:

      1. The dates to use in the calculation change based on the type of request and other Dimension values, per client.
      2. Each client can have their own SLA thresholds for each SLA

       

      My primary data source is an Excel sheet I have a live connection to, referred to as Master Data Source. I created a separate Excel worksheet (SLO) that defines the Start and End date field names to use for each SLA per client and have joined it to my master data in Tableau, so now I have all the dates and criteria I need in my Data Source. The SLO worksheet fields indicate which date Dimension from my Master Data to use when calculating the number of days between the two dates for each client.

       

      ClientL1-L SLO StartL1-L SLO EndL1-H SLO StartL1-H SLO EndL1-CA SLO StartL1-CA SLO End
      Client 1Date AcknowledgedDate Sent to ClientDate QualifiedDate Sent to ClientDate Sent to ClientDate Client Approved

       

      So for Client 1 above, to calculate the L1-L SLO I need to use the "Date Acknowledged" and "Date Sent to Client" fields in the calculation, as defined in the SLO worksheet.

       

      I want to create an Calculated Field that will use these dates in a DATEDIFF calculation and need to pass the L1-L SLO Start and L1-L SLO End dimension values to the formula for the formula to use the actual date dimensions "Date Acknowledged" and "Date Sent to Client" in the calculation. I have tried several ways of doing this but have not been successful in finding the correct way to pass the dimension name to use, to the formula.

       

      The calculation has to be performed without any user intervention based on the criteria defined in the SLO worksheet.

       

      Is there a way to do this or any suggestions on another way to accomplish what I need?

       

      Thanks

        • 1. Re: Passing Dimension Values to a Calculated Field
          Eric Hammond

          Hi Richard,

           

          Hopefully a more experienced community member will have the syntax to convert those values to field names.  As a back-up plan, use IF/THEN logic inside of the DATEDIFF function:

           

          DATEDIFF ('day'

          , IF [L1-L SLO Start] = 'Date Acknowledged' THEN [Date Acknoledged]

            ELSEIF [L1-L SLO Start] = 'Date Qualified' THEN [Date Qualified]  //etc.

            ELSE [Default Date Field]

          , IF [L1-L SLO End] = 'Date Date Sent to Client' THEN [Date Sent to Client]

            ELSEIF [L1-L SLO End] = 'Date Client Approved' THEN [Date Client Approved]  //etc.

            ELSE [Default Date Field]

          )

           

          Unfortunately, this will grow to be a fairly large calculation in order to account for all necessary scenarios.