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
    Claire Lofgren Jun 20, 2018 12:26 PM

      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

      Group

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

      1

      EquityB00
      Fixed IncomeC1NULL
      Fixed IncomeD1

      1

       

       

       

      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

       

      Group

      		% 1 Year% 10 Year
      Equity50%50%
      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!