10 Replies Latest reply on Dec 19, 2016 4:18 PM by Justin Lovern

    LOD to determine the Total Distinct Count up through a given date

    Justin Lovern

      I cannot provide a packaged workbook as the information cannot be shared, but I can attempt to describe the problem.

       

      Ultimately, I need to calculate the penetration rate of individual products. This is done by dividing the total number of members  in that product by the total number of members active members up to the row date:

       

      CategoryProductDistinct count of Person#Count of MembersYear of Contract_DatePenetration
      CNSINAUxy2014%
      xy2015%
      xy2016%
      INUAxy2014%
      xy2015%
      xy2016%

                                                                                

      CategoryProductDistinct count of Person#Count of MembersYear of Contract_DatePenetration
      CNSINAUxy2014%
      xy2015%
      xy2016%
      INUAxy2014%
      xy2015%
      xy2016%

       

      I am currently using {countd(Person#)} to get the total number of members, but this of course returns the all time total of all years. I just need the total up until the row date. For example, the distinct count of members who have opened an account (contract_date) up through 12/31/2014 would be the number in the 'y' in the first row.