Pivot functionality of smartsheet

Anirban Roy

Hello

I have an existing excel which does the below job using pivot table.

For each project iCode, for each RFC if we have same PO and same line item then we are adding the cost. Hence for the first 2 lines we are adding. Again for line 4&5 we are adding the cost. And we are putting this into a different excel. Also we are filtering based on month.

Is it doable in smartsheet using pivot table?

Thanks for your reply

Screenshot 2020-03-30 at 17.36.49.png

Genevieve P.

Hi @Anirban Roy

My apologies, I'm not sure I quite understand what you are doing. It sounds like we would be able to achieve a similar outcome by using formulas in Smartsheet to perform calculations (such as checking the iCode, RFC, and PO number and returning a specific value based on that).

Is any of your data imported into Smartsheet? If so, could you provide a full screen capture of the sheet (blocking out any sensitive data, or replacing it with examples) and explain exactly what sort of view you would like to see, or what sort of information you need populated.

Anirban Roy

Hi Genevieve

Thanks for your reply.

Please find the expected output as attached from the input given above.

sampleOutput.xlsx

Thanks again for helping me out.

Genevieve P.

Thank you for clarifying!

So to do this within the core Smartsheet app, you could create a new sheet with a chart similar to what you provided in the xlsx document, with the iCode, RFC, PO & LineItem listed. Then we would use a cross-sheet SUMIFS statement that will add together the costs if those 4 columns equal what's in this chart.

Here's an example of how you might write this:

=SUMIFS({Costs in other Sheet}, {iCode in other sheet}, iCode@row, {RFC in other sheet}, RFC@row, {PO in other sheet}, PO@row, {Line Item in other sheet}, LineItem@row)

You would place this formula in the COST column in this second sheet, and drag-fill the formula all the way down. Then it would evaluate the values in each row, and if there is a row that matches it in the other sheet, it will SUM together those values and return the overall cost.

The references {in these} are columns selected from the other sheet using Cross Sheet References. The names with @row are referencing the columns in the sheet with the formula.

You can read more about these different functions in our Help Center: SUMIFS function / Cross Sheet formulas / @row function

Let me know if this answers your question, or if there's something else I could help with. You may also want to chat to the System Admin for your plan (if you're on a multi-user plan) to see if your company has purchased other resources for you (such as any of our Premium Apps, Pro Desk sessions, or even Consultancy help).

Cheers,

Genevieve

Anirban Roy

Hi Genevieve

Thanks for your reply.

I understand the formula you have provided is only for the cost column.

However based on the above condition the other columns also appear accordingly.

Hence a question to you, how can I write the formula for other columns like iCode so that it repeats only that number of times which is necessary and it should not repeat the same number of times of the master sheet.

Genevieve P.

Hi @Anirban Roy

Perhaps I've misunderstood your question. Do you have access to the premium Smartsheet Pivot app? If so, you should be able to easily create a summary sheet using the Smartsheet Pivot App based on your source sheet that will roll up data for you, like this.

See our Help Center article on the Pivot app, here: https://help.smartsheet.com/articles/2476736

If you don't know how to set this up in our Pivot app, I would suggest checking with your organization to see if you have a license on a plan that includes Pro Desk sessions: https://www.smartsheet.com/pro-desk

There's a category for Premium Apps (the second one down) where you can book a 30 minute coaching session.

Anirban Roy

Hi Genevieve

Thanks for your reply.

Yes I do have access to Pivot functionality. However I am unsuccessful in achieving what i want.

From the input picture I need only 2 resulting lines.

Since the combination of the underlined items makes a unique combination, I need to add all the amount for the unique combination.

Screenshot 2020-04-02 at 14.58.36.png

Hence I need to have the result like this.

Screenshot 2020-04-02 at 15.06.30.png

I am trying with Pivot but it is not flexible enough in giving what I am looking for. Can you please help me to point out what should I select for Rows, Columns and values while defining the pivot.

Thanks a lot for your help.

Genevieve P.

Hi @Anirban Roy

The Pivot app in Smartsheet does have different functionality and visuals, as you have found. I would recommend going through the course on the Pivot app in the Center of Excellence as it has tutorial videos (see here. If you're not sure what your Access Code is, please contact the System Admin for your organization).

You could try setting up the workflow as follows for a SUM of costs:

The columns selected in the ROWS section will create a hierarchy in your sheet for the different criteria you want. I set the To Icode as the Parent value (the top criteria for how to organize the sheet), but you can adjust this if the PO number is what you want as the primary.

Then I have the Columns split into Months, as you had mentioned this in your original post. And finally, I selected what values I wanted to see: the Costs added together.

If this doesn't help, please review the course in the Center of Excellence and contact your System Admin to see if you have the ability to book Pro Desk sessions.

Anirban Roy

Hi Genevieve

Many thanks, it worked as expected.

So now I am able to get an aggregated view like this.

Screenshot 2020-04-02 at 16.42.11.png

However can you please suggest how can I create format like down below from the pivot table.

Screenshot 2020-04-02 at 15.06.30.png

Thanks again for all your help.

Genevieve P.

Hi @Anirban Roy

Try collapsing all of the rows - minimizing the child rows. To do so, right click on the Rows column name and choose the collapse all option from the menu. (See here for more information)

Is that what you meant?

Anirban Roy

Hi Genevieve

I am sorry that I could not explain last time.

Is it possible to convert from

Screenshot 2020-04-02 at 17.27.31.png

to that

Screenshot 2020-04-02 at 17.27.19.png

Thanks for your help.

Genevieve P.

Hi @Anirban Roy

Honestly I'm not very fluent in the Pivot app. I was able to check the account associated with your email address and I can confirm that you are eligible for ProDesk sessions as this was purchased as part of your company's Smartsheet plan. If the view or display from the Pivot App is not as you would like, I would suggest booking a Pro Desk session, choosing the "Connectors" category, to be able to have a screen share session for 30 minutes with a Smartsheet expert.

That said, I have come up with a potential solution by using the Smartsheet Core Application. It will require a few helper columns and workflows, but I think it will achieve your goal in the end.

We can use the Copy Row workflow to copy over specific rows to your second sheet, with the rollup data. But we need to tell this workflow which rows to copy over, so we need some helper columns.

To make sure we are only selecting unique combinations once, we can use a formula in a checkbox column that will look at your different columns and only check the box for the earliest created row with that exact same combination.

1 . Helper Columns:

We first need to make sure you have inserted a System Created column in your sheet that looks at when the row is Created (see here). Then, in a new checkbox column we can use an IF Formula to see if the date in the created column is the earliest date for that exact combination:

=IF(Created@row = MIN(COLLECT(Created:Created, [To Icode]:[To Icode], [To Icode]@row, RFC:RFC, RFC@row, [PO Number]:[PO Number], [PO Number]@row, Position:Position, Position@row)), 1, 0)

This formula looks at each of these columns for that row: [To Icode] / RFC / [PO Number] / Position

Then it checks the box for the row that has the earliest date where this unique combination occurs. Keep in mind that if two rows with the same combination were created in the same minute, it may check both of these boxes.

2 . Copy Rows:

Next, set up a workflow to Copy Rows to another sheet if that box is checked. (see here)

In your second, rollup sheet you will need to have the exact same columns as in this sheet. However, you can hide any columns that you don't want shown (see here), especially the COST column from your other sheet. Instead of that column, we want to have a new TOTAL COST column set up.

3 . Total Cost Formula

Once you have hidden all the columns that are not needed, the final step is to create a new Total Cost column in this rollup sheet. This is where you can use my previous formula that looks at the criteria in the other sheet and sums it up:

=SUMIFS({Costs in other Sheet}, {iCode in other sheet}, iCode@row, {RFC in other sheet}, RFC@row, {PO in other sheet}, PO@row, {Line Item in other sheet}, LineItem@row)

This is the only way I could think of achieving the view you would like, however someone else in the Community may have another idea. 🙂

Hope that helps!

Cheers,

Genevieve

Anirban Roy

Hi Genevieve

Thanks for the answer.

I have setup a pro desk session in the coming week.

In the meanwhile I have achieved something like what I attached here.

Screenshot 2020-04-06 at 11.56.13.png

How can I get only 2 rows out of it. i.e. only the unique combination.

I410189239-CHG0098020-49006766756-01.2020 5016

IU13317-CITE-1-49006766754-01.2020 2981

Thanks again for all your help.

Genevieve P.

Hi @Anirban Roy

You can adjust the formula above to be looking for different criteria. So change the Step 1 formula above to be something like this:

=IF(Created@row = MIN(COLLECT(Created:Created, test:test, test@row)), 1, 0)

It definitely makes the formula shorter, which is great!

Then in Step 3 above, you don't need to match all the different columns - you can just look for that one unique cell. This does mean using a singular SUMIF formula instead of SUMIFS, so the structure is backwards: Criteria Column, unique Criteria in this sheet, then the Column to Sum.

=SUMIF({Unique Name Column in other Sheet}, [Unique Value]@row, {Costs in other Sheet})