LostInDaJungle
New Member
- Joined
- Feb 27, 2012
- Messages
- 47
I have a series of accounts and period balances coming from a database.
Each line is basically:
Year - Month - Account - Netchange
I'm using Pivot tables to summarize this information for YTD calcs.
I put "NETCHANGE" into the values of the PivotTable and set the name to YTD and the Set the type to running total by month.
I can then call an individual account like so:
=GETPIVOTDATA("YTD",ptAll,"FISCALYEAR",2011,"FISCALPERIOD",12,"ACCOUNTNO","10000000")
It works like a dream except for those odd accounts that don't get much activity. Some accounts did not have a change in December of the target year, so there is no PivotTable line for 12/2011.
I've played with all of the PivotTable options that would seem to "fill in blanks" and none work for me. How can I get my PivotTable to treat empty months as a 0 netchange and give me a total for December?
Here's what my pivot table looks like:
Thanks in advance for your help.
Each line is basically:
Year - Month - Account - Netchange
I'm using Pivot tables to summarize this information for YTD calcs.
I put "NETCHANGE" into the values of the PivotTable and set the name to YTD and the Set the type to running total by month.
I can then call an individual account like so:
=GETPIVOTDATA("YTD",ptAll,"FISCALYEAR",2011,"FISCALPERIOD",12,"ACCOUNTNO","10000000")
It works like a dream except for those odd accounts that don't get much activity. Some accounts did not have a change in December of the target year, so there is no PivotTable line for 12/2011.
I've played with all of the PivotTable options that would seem to "fill in blanks" and none work for me. How can I get my PivotTable to treat empty months as a 0 netchange and give me a total for December?
Here's what my pivot table looks like:
![Budgeting+Model+Package.xlsx+872012+30113+PM.jpg](/board/proxy.php?image=https%3A%2F%2Flh4.googleusercontent.com%2F-2d12eg72yKs%2FUCFlgrXQ9UI%2FAAAAAAAAAuM%2Fvq-0waL_pAE%2Fs372%2FBudgeting%2BModel%2BPackage.xlsx%2B872012%2B30113%2BPM.jpg&hash=6ce7eecee0d87b726c7c9fece392d687)
Thanks in advance for your help.