Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Dynamic pivot table column addition

Answered Dynamic pivot table column addition

  • Tuesday, June 12, 2012 1:51 AM
     
     
    Sign In to Vote
    0
    Hello, so I'm looking at some data that deals with number of people being worked with and a date period with which this will happen. The problem is that sometimes the period is not sufficient so I want to show how many people would overflow and if so, to which months. I am displaying the current information on how many people per day can be served and how many total will overflow in a pivot chart and am wondering, is it possible to dynamically add columns representing months for the overflow to the pivot table? If so, how?
     

All Replies

  • Wednesday, June 13, 2012 5:20 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi，

    I want to confirm with you that the Pivot Table will show the months which overflow and the how many total will overflow, am I right?

    If what I understand is right, I think you can add a column (named as overflow_month) and use a formula in the table to judge whether the data is overflow. And if the data is overflow, then the column overflow_month will show the month. Then make the Pivot Table show the month.

    Note: the only shortage is that the months which doesn’t overflow will show ”blank” in the Pivot Table.

    The formula in column overflow_number is:

    =IF([@[People_number]]-[@[Max_number]]>0,[@[People_number]]-[@[Max_number]],"No number")

    The formula in column overflow_month is:

    =IF([@[overflow_number]]>0,[@Month],"")

    Jaynet Zhang

    TechNet Community Support