Pivot Table App: Formula + Additional Issues

haznedarj

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.

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