1 Reply Latest reply on Apr 18, 2012 12:42 AM by Jonathan Drummey

    COUNTD calculation unique over multiple years

      I am using the COUNTD function to uniquely count policy numbers that have premium in the first year.  For example, Policy #9 has $42.49 of premium in 2011.  This policy is counted once in 2011.  This calculation works fine for 1 year. 

       

      However, when the same policy remits premium over multiple years, the COUNTD function includes the policy number again in each years policy count.  For example, policy #1 has $193.45 in premium in 2011 and $34.22 in premium in 2012.  I only want to count the policy in the first year that we received premium.  Any subsequent year, I do not want to count the policy.  In this example, the policy is included in the count in both years, 2011 and 2012, because premium was received in both years.  

       

      In the attached example, the policy count in 2011 is 9 and in 2012 is 9.  The policy count should be 2 in 2012 since 7 of the policies remitted premium already in 2011.  In the far right, I showed what the result of the calcuation should be.

       

      Is it possible in Tableau to create a calculation that will only count the policy once over multiple years even if premium is received in future years?

        • 1. Re: COUNTD calculation unique over multiple years
          Jonathan Drummey

          Hi Tricia,

           

          Without seeing some sample data to get a sense of how it's set up I'm not able to give you an exact answer. I suspect this is part of why you haven't gotten a response on the forums yet, questions like yours can potentially have several different solutions that are each dependent on how your data is organized.

           

          For example, the spreadsheet you provided has the years as different columns, is that the case in your data or is it stored as one row per policy year?

           

          Jonathan