• 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 calc. field considering value from previous field

wintermute

New Member
Dears,
problem which bothers me for a long time.
I have an access database with raw data for purchasing.
I'd like to simply calculate weekly balance for buys ("Previous week stock/balance" plus "Incoming" minus "Demand") weekly, considering stock from previous week. I think it cannot be calculated in Access, as Access cannot reference to value in previous record... I don't want to calculate it standard way with functions on a sheet, I want to keep all data in access and pull them into excel's pivot table (as items count from 100 one week to 3500 next week because of long running items and short, one_time_buy items) therefore I don't need to maintain rows with dead, obsolete functions or, on the other hand, copy functions to new rows, if new items arise. I tried to solve it with "Running total in" in pivot, but it's not what I need. I can see only one option now: pull in data from Access to Excel in pivot table, change pivot table to regular table, filter subtotals and put functions manualy into the cells, but you know, it's not elegant and proper way. Is is possible to reference in pivot calculated field value from previous week? Or is it possible to calculate it somehow in Access?
I hope you understand my english and thank you in advance. Sample file attached.
 

Attachments

  • pivot_running_subtotal.xlsx
    15.3 KB · Views: 3
You can kind of show subtotal in the way you want... but that will bugger up each item.

upload_2016-4-1_20-41-49.png

This is accomplished by Sum of Qty -> Show Value As -> Running Total In
Base Field "Week".

Collapsed it will look like.

upload_2016-4-1_20-46-27.png

Hmm maintaining 2 Pivot may meet your need. One with item broken out and one with total for each Item only.
 
Back
Top