10 Replies Latest reply on Jun 20, 2018 1:56 PM by Deepak Rai

    How do I count the number of records within different time periods?

    Claire Lofgren

      Hi there,


      Hoping I'm just missing what's a simple answer.


      So the underlying data, table one, shows 2 groups with included investments. The two following rows are 1 if the investment beats the benchmark, 0 if it does not; null is if the investment doesn't have a 10 year number (too young of an investment, database doesn't have it, etc,)


      TABLE 1


      InvestmentBeat its benchmark 1 Yr.Beat its benchmark 10 yr.


      Fixed IncomeC1NULL
      Fixed IncomeD1





      What I need to show is: what percent of those investments beat their unique benchmarks, at the group level (table 2).


      SO: (Number of Investments that beat benchmark) / (number of investments that have that information available.)


      TABLE 2



      % 1 Year% 10 Year
      Fixed Income100%100%



      Here's my issue. I want to be able to hover over that 100% number in table two, and have the detail tell me that within the 10 year set, there was only 1 record in the denominator, since investment C didn't have a 10 year number. But number of records gives me the number of rows, so it counts the number in the set even if the 10 year is null.


      Is this a data structure issue or something I can do with a calculated field?


      Many thanks & let me know if I need to clarify!