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

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 Name No. of Intercepts No. of Notices Issued  (Distinct count of REPORT_NO) Non-compliance rate No notices issued 9655 0 0 Licence Advice 14 0.15% need assistance in writing calculation for these Driver Direction 18 0.19% Formal Warning 21 0.22% Unregistered Vehicle Advice 22 0.23% Secure/Adjust Load 140 1.45% Breach 250 2.59% Traffic Infringement Notice 257 2.66% Defect Notice 2,197 22.76% TOTAL 2919 30.23% I can write calculation for this
• 1. Re: How to write a calculation that only uses a subset of data

Will This Work?

Thanks

Deepak

• 2. Re: How to write a calculation that only uses a subset of data

Thanks Deepak. You're a genius!

That's exactly what I need.  Thanks for your help and quick response.

• 3. Re: How to write a calculation that only uses a subset of data

You are Welcome

• 4. Re: How to write a calculation that only uses a subset of data

Hi Deepak,

Can I ask you a further question?

If I wanted to report the non-compliance rate for each notice by day, how is this done? Dragging the 'local intercept time' dimension onto the view doesn't work.  Thanks

• 5. Re: How to write a calculation that only uses a subset of data

This is breakup of breach by day, Pl Check Screenshot and attached.