Limit Columns in a Pivot Sheet

Happy Holidays-

I am looking to use Pivot to create an updating table that will give me a source to use for a trend line graph for admissions over the last 15 days.

My only problem is that I don't know how to stop Pivot from adding columns as the trend gets longer. It may be that Pivot is the wrong tool for the job.

Here's what I've planned so far:

Step 1: Source sheet with a date column for admissions

Step 2: Helper column that measures how long ago admission date was (TODAY()-[Admission Date]@row)

Step 3 (Here's the problem): I would like to Pivot on the source sheet as follows-

Rows- Office

Columns- Days ago (but only up to 15 to have a 15 day trend)

Value- Days Ago-Count

My goal is something like this, going out to 15 days:

From my testing, when I get to day 16, Pivot just adds another column.

The only alternative that I've been able to come up with is to build a second sheet, write COUNTIFS formulas for every office, and chart from that. The limitation to this is that it must be manually updated if we add a new office.

Thanks for any help.

Find more posts tagged with

Accepted answers

Kelly Moore

Hey @AFlint

I'm not sure how your data is laid out but is it possible that you can build a report with the 15 days that you need? Then use this report as the data source for the pivot table - although the pivot table field says Sheet you can insert Reports.

Would that work for you?

Kelly

All comments

Kelly Moore

Hey @AFlint

Would that work for you?

Kelly

AFlint

Hi @Kelly Moore

Thank you for the reply!

I'm trying to picture the workflow for your suggestion. Please tell me if I'm on the write page

Step 1 and 2 as described in the original post

Step 3: Create a report with filters that only pull in the rows in the 15 day range that I described

Step 4: Generate a pivot from the report. This would allow me to arbitrarily limit the Pivot to 15 days because there would never be data greater than 15 days due to the filter on the report?

Is that about right?

Kelly Moore

@AFlint

Yes, that’s exactly what I imagined. I frequently use reports to filter my data for pivot tables, for example only pulling current year data from a multi year sheet.

Depending on how your sheet data is formatted, you may need a helper column for the report filter- a checkbox column could designate if row was in your dataset, or you could extract the day into a column and filter on <15. Again, it will depend on your sheet data and what filter options are available. If you need help with helper-column formulas, shout out

Kelly

AFlint

@Kelly Moore

I built a test and it seemed to do what I wanted, so thank you. Since it will always be a fixed 15 days, it will also let me generate a chart on my dashboard without worry about any change in the source data structure.

Thank you for the help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.