Dynamic restricted date range in a chart
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?
Best Answer
-
The "@cell" reference basically tells the function to evaluate the range on a cell by cell basis.
Answers
-
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
-
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?
-
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.
-
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
-
Try something like this...
=COUNTIFS({Database Date Range}, @cell >= TODAY(-91), {Database Lead Type Range}, "Referral")
-
That did it!!! Thanks. Not entirely sure what that @cell thing is all about but when I get time, I'll figure it out.
-
The "@cell" reference basically tells the function to evaluate the range on a cell by cell basis.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives