Running Total in Pivot Table Smartsheet

Carolina Asensio

Hey!

I have a form linked to a sheet, and everytime someone replies to the form they are "raising a question". We have people working to solve these questions, and once this is done they change the status of this question to "answered" by checking a box in the sheet.

I have the following columns:

(1) Form submission date

(2) Questions (each line is a different question)

(3) Questions answered (checkbox if the question has been solved)

From an analytics perspective, it would be of our interest to display a graph in a Dashboard with the following:

X-axis: time (in this case the dates of responses submitted by a form)

Y-axis: number of question (both raised and answered)

The function being displayed would be a RUNNING TOTAL instead of just a count of questions raised and/or answered each day.

So for example, by the end of the week there would be a total of 10 questions asked (maybe 2 on monday, 3 on tuesday... and so on) and it would be an increasing function of time (since on monday it would be 2, but on tuesday it would now be 5 (2 on monday and 3 on tuesday) and so on). And on another line in the same graph we would like to display the running total of questions being answered by agents. Our main aim is to see graphically that initially the function for "questions raised" is increasing steeply and then is stagnating over time since users get more familiar with the new tool and don't ask too many questions, and the function for "questions being answered" will be slowly increasing initially and keep increasing at a constant rate until at some point it also stagnates since there will not be that many new questions to answer.

Long story short, I tried doing this with a Pivot Table (as in excel) but Smartsheet only offers the possibilities of "sum", "average" and "count" and does not offer "running total". Is there any workaround this or some other way of doing so without Pivot Tables?

Thank you so much!!!

Paul Newcome

Are you able to provide a screenshot of a mocked up end result that you are trying to achieve?

Carolina Asensio

Something similar to this (the orange line representing the cumulative sum of sales would be in this case the cumulative questions that have been asked over time) and then add a second line with the cumulative answers that have been given.

Paul Newcome

But you want it to be a running total, correct? Were you wanting to maintain the same start date for your data and add new days as they come thus increasing the number of points or were you wanting something more along the lines of rolling where it just shows the past 6 months or 3 weeks, etc?

Carolina Asensio

Exactly!! The start day would be fixed, and we would be adding lines for the next few months (around 6).

Paul Newcome

Ok. So far this is doable except for one detail... We cannot combine chart type. You would either need to use lines or bars. Is that acceptable?

Carolina Asensio

Yes!!