Forum Discussion
Operation using the previous row data
I'm not sure why you think it would take 4 times... but I don't see your dataset.
Another option is to adjust the week no for closing balance rows to match the other rows, and then do a pivot to get a column for each event type in a week.
Hi Artemus, I must not understand properly your steps then...
Here is the data I'm working with:
A query with my week numbers
A query which has my total pallets in and total pallets out for the different weeks of my data set (some weeks might be 0 and therefore don't show up in that table)
A query which is drilled down to return the opening balance for the first week.
If Week number is n, n+1, etc... I need to create the following table:
Where Opening balance week n is OPWn, Storage is SWn, Closing balance is CBWn
Pallets in week n is PIWn, Pallets out Week n is POWn
Opening balance for the first week is x (manual input)
| Week number | Opening balance | Storage | Closing balance |
| n | x | SWn= x + PIWn | CBWn = SWn - POWn |
| n+1 | OPWn+1 = CBWn | SWn+1 = OPWn+1 +PIWn+1 | CBWn+1 = SWn+1 - POWn+1 |
| n+2 | OPWn+2 = CBWn+1 | SWn+2 = OPWn+2 +PIWn+2 | CBWn+2 = SWn+2 - POWn+2 |
I hope that makes more sense and sorry if the solution is the same, I just can't work out what your instructions mean for my data...