1 Reply Latest reply on Nov 20, 2013 3:22 PM by Patrick A Van Der Hyde

    Calculations with multiple date fields

    Mike Dimond

      Goal:
      Add reports to a dashboard that can show the defect trend information over a time period. Immediate task is to show the number of defects created, closed, net change and cumulative total number of open defects for each month.

       

      Problem Statement:

      Each defect contains two types of dates, Created Date (when defect was created) and Modified Date (when defect was closed if it is in a status of "closed"). My challenge is creating calculations that can count the correct number of created and closed defects for each month. For example, when I put Created Date in the rows shelf the calculations only return results based on the Created Date counts. I am able to calculate each correctly on a separate tab, but want to have the data consolidated in a single table.

       

      Data:

      The attached Excel workbook has 14 mock defects. The created date represents when the defect was opened, whereas the modified date would represent when the defect was closed if it is in a closed status. J8 through S12 represent the expected results for the report in Tableau. The Tableau packaged workbook contains three tabs: Problem, Created and Closed. The problem tab is the one that I need assistance with; Created and Closed tabs show how the results can be calculated separately, but do not work together.

      Note, I am using Tableau Desktop Version 7.

       

      Expected Results:

       

      Deferred DefectsProduction Defects
      MonthCreatedClosedTotal ChangeCumulative DefectsCreatedClosedTotalCumulative Defects
      January31223122
      Feb31245235
      March01-130005

       

       

      Actual Results:

       

      Deferred DefectsProduction Defects
      MonthCreatedClosedTotal ChangeCumulative DefectsCreatedClosedTotalCumulative Defects
      January22002200
      Feb33006600
      March11000000

       

       

       

      Thank you in advance for any assistance.