5 Replies Latest reply on Apr 20, 2017 7:07 AM by Tushar More

    Insurance Contract Coverage over a Product Install Base using Complaint Case Data

    Guy Bennett

      I am trying to report on the level of contract coverage on a product (by region and in total). Here is an extract of the source data (for illustrative purposes) :

       

      CasenumberRegionSerial NumberContract StatusContract Active?
      3133993UKM1Active1
      5226081UKM1Active1
      1656185USAH5Expired0
      6074179UKH2Expired0
      3522648UKH2Expired0
      4724872IndiaA9Active1

       

      Each row represents a customer complaint and has a unique case number. Each product has its own unique serial number, but a product may be listed multiple times due to multiple complaints.

       

      I would like to use Tableau to report on the level to which the products are covered by an active contract. I.e. The percent of products in total (and by region) that have an active contract. For the data above this would be, in total:

       

      Unique Products: M1, H5, H2, H9.

      Number of those covered by a contract: 2 (M1, A9)

      Coverage: 2/4 = 50%

       

      For the USA the coverage would be 0% and for India it would be 100%, based on the above table.

       

      It seems simple, and perhaps it is, but I am having problems doing this with Tableau. Getting Tableau to display both the number of unique products and the number of those that are covered by a contract, on the same worksheet, very difficult! I have experimented with an LOD calculated field, but not solve the problem yet. Perhaps I am not thinking about this in the right way. Any help would be much appreciated.

       

      I have attached a .twbx (created using Tableau Desktop 10.1) with an expanded data set. The data source has two tables - the raw example data and also a table containing what the answers should be (calculated manually).