5 Replies Latest reply on Oct 8, 2017 10:43 PM by Deepak Rai

    How to write a calculation that only uses a subset of data

    Andrew Klein

      Hello,

       

      I'm hoping someone will be able to assist in explaining how to write a calculation within Tableau that only uses a subset of data.

       

      The data I'm using consist of vehicle intercepts and notices (fines) that are issued to vehicles during an intercept.

      During an intercept a vehicle can be issued with any number of 8 types of notices (i.e. a defect notice) or they may be issued with no notices.

       

      I would like to calculate the non-compliance rates for each individual notice type i.e. how many intercepts resulted in a defect notice being issued. For this example the calculation is "number of defect notices issued DIVIDED BY number of intercepts.

       

      I can write the calculation  COUNTD([Report No])/COUNTD([Units Intercepted])    that calculates the overall non-compliance rate for all notices combined, however when I apply this calculation to each individual notice type it returns the wrong result.

       

      Below is an excel table of the number of intercepts, notices issued, and correct non-compliance rate by notice type.  The attached Tableau packaged workbook contains sample data. you will notices that the results within the 'Non-compliance' tab do not match the below.

       

      Any assistance would be greatly appreciated. Thank you in advance.  

       

       

       

      Notice Cat NameNo. of InterceptsNo. of Notices Issued  (Distinct count of REPORT_NO)Non-compliance rate
      No notices issued965500
      Licence Advice140.15%need assistance in writing calculation for these
      Driver Direction180.19%
      Formal Warning210.22%
      Unregistered Vehicle Advice220.23%
      Secure/Adjust Load1401.45%
      Breach2502.59%
      Traffic Infringement Notice2572.66%
      Defect Notice2,19722.76%
      TOTAL291930.23%I can write calculation for this