• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot Setup

Gregg Wolin

Member
I am planning a model that requires the categorization of a variety of costs and revenues into a hierarchy and spread out over various periods of time (illustrated below and in the attached file).
 

Attachments

  • PivotQuestion.xlsx
    27.2 KB · Views: 2
Gregg Wolin
Many things are possible
... after You would know exact ... what? - without etc
One sample ...
There were some/many 'Circular References', but I skipped those.
 

Attachments

  • PivotQuestion.xlsx
    25.6 KB · Views: 1
Gregg Wolin
Many things are possible
... after You would know exact ... what? - without etc
One sample ...
There were some/many 'Circular References', but I skipped those.
My problem is the spreading of the line item amounts across the start and end dates (which would have been clear w/o the circrefs - i apologize). Here is a better annotated working version.
 

Attachments

  • PivotQuestion.xlsx
    25.9 KB · Views: 1
Gregg Wolin
Next version
... there are still many many other possibilities.
Thanks - but it still isn't spreading the costs between the Start Date and End Date. It needs to divide the amount of the line item by the number of months between the start and end date and show that amount in the requisite month. i.e. For a $600k line item, if the Start Date is June 2020 and the End Date is 12/31/2020 then there should be 100k in each month between June and December 2020.
 
Gregg Wolin
Do You still write about Pivot-table?
For me, Pivot-table could show values from data many ways.
But ... Your way to show those values could show other ways ... eg by code.
but but ... then You should able to write very clear, what would You want to see?
 
Gregg Wolin
Do You still write about Pivot-table?
For me, Pivot-table could show values from data many ways.
But ... Your way to show those values could show other ways ... eg by code.
but but ... then You should able to write very clear, what would You want to see?
Sorry Again - I forgot to repost the file (notes inside). If its not do-able with a pivot table, then vba or other options would be fine.
 

Attachments

  • PivotQuestion (1).xlsx
    31 KB · Views: 1
Back
Top