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?

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!

• ###### 1. Re: How do I count the number of records within different time periods?

Hi Claire,

Here It Is:

These are The Calcs:

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to close Thread

• ###### 2. Re: How do I count the number of records within different time periods?

Thanks Deepak,

This seems like the right track, but is there any way to get those counts (the two right rows) as details per column? as in, hover over a cell and it will tell you how many are in the set?

• ###### 3. Re: How do I count the number of records within different time periods?

You Can Add Those Into Tool Tip. You can Just Drag Them into Tool tip. Can you Please Take a  sec to Close Thread by Marking my Reply as Helpful and CORRECT. The CORRECt Button is under my Reply in this main Thread, Not in email.

Thanks

Deepak

• ###### 5. Re: How do I count the number of records within different time periods?

I guess I wasn't clear, sorry!

I do not want a universal tooltip that lists all of the different time periods. I will end up having about 10 time periods and don't want to clog the tooltip that way.

I'm looking for something like a "number of record" field that changes as you scroll across the columns. Basically looking at the denominator of each calculated field

• ###### 6. Re: How do I count the number of records within different time periods?

Pl see attached and you can modify accordingly. Your Question  was a Good Question, Though, Little Complex

Thanks

Deepak

1 of 1 people found this helpful
• ###### 7. Re: How do I count the number of records within different time periods?

That doesn't quite do it, unfortunately. So, right now this is how the tooltip shows.

Which I had, but the question really is how do I show "Den 10" count only in the 10 year column?

In other words, in the tool tip below, I would not want to see the count for "den 1" just "den 10) and vice versa.

• ###### 8. Re: How do I count the number of records within different time periods?

I am Sorry, I Don't Think You Can Do That Way...I have Not Come across That Thing...But, You can Create a Separate thread for that after Closing It so that Someone can Show you how to get that.

Thanks

Deepak

• ###### 9. Re: How do I count the number of records within different time periods?

Ok, I’ll leave this open though. Thank you for your efforts!

Best,

Claire

• ###### 10. Re: How do I count the number of records within different time periods?

Well....That may not be Helpful, as your Initial Question was Answered So People Might Not Go Down To the End To answer the Supplement, So It is always better to Create a New Thread, you can attach the Packaged Workbook Which I attached and with New Header, That way you would get Help faster.

Thanks

Deepak