Pivot Table App: Formula + Additional Issues

Greetings,

Issue 1: I have created a pivot sheet using the Pivot App within SmartSheets. Once the pivot is created I am adding additional columns to the pivot sheet, which have formulas embedded. I noticed that when adding a new row of data the formula does not propagate in the pivot sheet, I am forced to manual drag it down. I thought this is something that should happen automatically by default. (note there are only parent rows no child rows are in the source sheet)

Issue 2: I have set the update frequency of the pivot sheet to immediate, however, this is not the case. It takes quite some time to update >>> 15 min. Any ideas or settings that I need to think of here?

Issue 3: The pivot also creates a Grand Total summary at the bottom of the column. Is there a way to turn this off. I know in Excel you can, but the settings within the Pivot App seem to be very limited.

Any help and insight would be much appreciated.

Find more posts tagged with

Integrations

Comments

Anthony M.

Hello!

Thank you for you reaching out on our Community site. I'm happy to take a look at these questions and provide some additional information around them.

Issue 1: I have created a pivot sheet using the Pivot App within SmartSheets. Once the pivot is created I am adding additional columns to the pivot sheet, which have formulas embedded. I noticed that when adding a new row of data the formula does not propagate in the pivot sheet, I am forced to manual drag it down. I thought this is something that should happen automatically by default. (note there are only parent rows no child rows are in the source sheet)

A: I put together a quick test for this and it should work as long as the scenario falls into what is described here https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting Should this not be working correctly for you, we will need to dig into your setup and sheet a bit more. I've added a link to contact support in the answer to Issue 2, please feel free to open a ticket directly and we are happy to take a look into this further for you.

Issue 2: I have set the update frequency of the pivot sheet to immediate, however, this is not the case. It takes quite some time to update >>> 15 min. Any ideas or settings that I need to think of here?

A: This delay is likely related to how many other configs are being processed at that time. Processing configs during peak times of the day may result in longer than expected wait times. If you are seeing this issue consistently, please feel free to open a ticket directly with our support team and include specific times(including date and timezone). As well as the time it took to run automatically. https://help.smartsheet.com/contact

Issue 3: The pivot also creates a Grand Total summary at the bottom of the column. Is there a way to turn this off. I know in Excel you can, but the settings within the Pivot App seem to be very limited.

A: There currently is not a way to disable this. Please feel free to file an enhancement request on this feature: https://app.smartsheet.com/b/form?EQBCT=739aa75f30ca43a8a22eb53e4da7d409

Thank you for your time!

Kind Regards,

Anthony

Smartsheet Support

Jonathan D.

Old thread but I'm going to comment on issue 3:

You can create a report using a pivot sheet as the base data. From there you can filter out the Grand Total column, apply any other filters and do any kind of sorting you need. I do this when I want to create dashboard charts based on pivot data using the report (rather than the pivot itself) as the data source.

This method using reports to shape the dataset is especially helpful when your pivot contains hierarchy. I add add a helper column to the pivot to determine whether or not the row has ancestors and then apply filters in the report to remove the identified parent rows.

Best,

Jonathan

Sign in to join the conversation!

Quick Links

Trending discussions

Anyone getting JIRA API errors using the connector?
We are using the Jira/Smartsheets connector and got this error: Invalid response from JIRA. statusCode: 410, reasonPhrase: Gone The requested API has been removed. Please migrate to the /rest/api/3/search/jql API. A full migration guideline is available at Looks like the connector needs to be updated. Anyone know anything…
Dirty save - #BLOCKED/Invalid Value issue when using integration until manually save the sheet
Hello everyone, We’re facing a critical issue in one of our Reports. We use integration and it adds values to the sheets. The corrected values don't seem to auto-save. Formulas in sheets first show #BLOCKED/Invalid Value, then if we refresh the sheet - it updates to the correct numbers. The save button is active even…
Smartsheet for Google Drive doesn't seem to be available anymore
Trying to access Smartsheet for Google Drive and it seems it's not available anymore: https://workspace.google.com/marketplace/app/app/46145466229 Also the app is not listed when searching on Google Marketplace.