Dynamic restricted date range in a chart

hughstearns

I have a sheet with several years of client data on it. Using a second table using count if, I have created a breakdown of lead types. I assume that this second table is dynamic to the extent that it will update as I add data. But what I want is to create a chart that will dynamically show this information for the last 91 days. Is there a way to do this, short of creating a new table every time I want to see this chart?

Paul Newcome

The "@cell" reference basically tells the function to evaluate the range on a cell by cell basis.

Kelly Moore

Hey @hughstearns

Charts can use reports as a data source. You should be able to use the report filter to gather the data. Below is one way of collecting data 91d old.

After selecting the report you will have the option to select which columns within the report will be used as your chart data.

Will this work for you?

Kelly

hughstearns

I'm not sure if this will work. I need to sort text information and then count it, turning it into numeric information that can be graphed. It seems that I can do one of those things but I am struggling with doing them both. I can sort in a report, but, it seems, I cannot get that sorted information into another sheet to count it and turn it into a graph. Right?

Paul Newcome

Create your metrics sheet with a date type column. The first date would be

=TODAY()

from there it would depend on exactly how you wanted the data displayed in the chart, but essentially you would then use cell references going down the column to output what amounts to a series of rolling dates going down until you have your 91 days.

Then you can include the date criteria in your COUNTIFS.

If you are not worried about daily/weekly breakdowns and just want to count backwards from today, then you would incorporate the date range/criteria into your COUNTIFS.

If you are able to provide some screenshots that shows what you are working with and explains exactly what you want to be dynamic, we may be able to help with a more detailed solution.

hughstearns

There are several lead types and hundreds of records. The ultimate goal is to show a pie chart of lead types for the last 91 days.

My failed attempt: =COUNTIF(and({Database Range 2}, <=TODAY(-91)), ({Database Range 1}, "Referral"))

Where "Referral" is a lead type

Paul Newcome

Try something like this...

=COUNTIFS({Database Date Range}, @cell >= TODAY(-91), {Database Lead Type Range}, "Referral")

hughstearns

That did it!!! Thanks. Not entirely sure what that @cell thing is all about but when I get time, I'll figure it out.

Paul Newcome

The "@cell" reference basically tells the function to evaluate the range on a cell by cell basis.