Pivot app - and Count function

I've created a pivot table to summarize the number of task completions in a month. From this, I want to calculate the average monthly completions.

The problem is, the table does not put a '0' in the months with no completions, so the averages are not calculated correctly (see below - 1 isn't the monthly average when only 1 was completed that year).

Maybe the answer is 'just add the zero' - but isn't 0 a real count?

When I formatted my pivot table and just used the sum function for the child rows - you can see it isn't counting blanks as months.

I tried changing my formula on the source sheet that is wrapped in an IFERROR to force blanks - changed it to force "0" - but that isn't it.

As a workaround, I've made automations to change blank cells to 0, but I'm thinking that 0 should just be a number that is automatically input on the pivot table.

I wanted to check in with the SmartSheet community to see if I'm missing something.

Thank you!

Meredith

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Meredith Rhodes

    I hope you're well and safe!

    Can you share the formulas used and some more screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi @Andrée Starå!

    I'll see if I can add more information here:

    This is a screenshot of the source sheet with the Type of Submission, Date Submitted, Date Completed Columns - and the helper columns I've created for Month and Year Submitted and Month and Year Completed (I'll add the formulas below)

    Month and Year Submitted

    =IFERROR(IF(MONTH([Date Submitted]@row) = 1, "January", IF(MONTH([Date Submitted]@row) = 2, "February", IF(MONTH([Date Submitted]@row) = 3, "March", IF(MONTH([Date Submitted]@row) = 4, "April", IF(MONTH([Date Submitted]@row) = 5, "May", IF(MONTH([Date Submitted]@row) = 6, "June", IF(MONTH([Date Submitted]@row) = 7, "July", IF(MONTH([Date Submitted]@row) = 8, "August", IF(MONTH([Date Submitted]@row) = 9, "September", IF(MONTH([Date Submitted]@row) = 10, "October", IF(MONTH([Date Submitted]@row) = 11, "November", IF(MONTH([Date Submitted]@row) = 12, "December")))))))))))) + " " + YEAR([Date Submitted]@row), "")

    Month and Year Completed

    =IFERROR(IF(MONTH([Date Completed]@row) = 1, "January", IF(MONTH([Date Completed]@row) = 2, "February", IF(MONTH([Date Completed]@row) = 3, "March", IF(MONTH([Date Completed]@row) = 4, "April", IF(MONTH([Date Completed]@row) = 5, "May", IF(MONTH([Date Completed]@row) = 6, "June", IF(MONTH([Date Completed]@row) = 7, "July", IF(MONTH([Date Completed]@row) = 8, "August", IF(MONTH([Date Completed]@row) = 9, "September", IF(MONTH([Date Completed]@row) = 10, "October", IF(MONTH([Date Completed]@row) = 11, "November", IF(MONTH([Date Completed]@row) = 12, "December")))))))))))) + " " + YEAR([Date Completed]@row), "")

    (NOTE: I've wrapped it in an IFERROR, then leave it blank - I've changed this to be "then make it 0", but this doesn't change the result)

    I'm attempting to summarize the average submissions and completions per month. Which means I need a count of the number of submissions and completions per month. In the pivot table for Completions, I've used the following:

    Rows: Month and Year Completed

    Columns: Type of Submission

    Values: Month and Year Completed (COUNT)


    This is the Resultant table after I preview and click "Create":

    ^^^ Of course it requires some formatting to get the dates in order, ADD months that aren't accounted for, and create parent child relationships the Year - but when you do this and insert the =AVG(CHILDREN()), it does not calculate the blanks as zero (it's not an average).

    Ideally - I get 0's in the blank cells to to make calculations work (and I've done that via an automation on the submissions table). Why would the Count Function in the pivot table think the absence of completions or submissions in a month isn't a count of zero?

    Thanks!

    Meredith

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Meredith Rhodes

    You're correct: if there is no data for the Pivot to calculate on, it will simply return a blank cell for that Type column as the COUNT value instead of 0.

    Your note of using workflows to add 0's to any blank cells is exactly what I would suggest for this scenario!

    Cheers,

    Genevieve