I could really use a little help... I'm trying to make an inventory report based on open SO's and PO's. The rows would be the item name and the columns would be dates (truncated to week) from this week through 16 weeks in the future. I want to see qty on hand today and what we have on the books to go in and out each week. That part is all fine. What I'm having trouble with is the projected balance. I can do a pivot report in suiteanalytics workbook, but can't figure out how to get the rolling total. Similarly I can write case statements to make the weekly totals work in a saved search, but I want the column labels to be the date of the week. I need the column labels to dynamically update, much as a pivot table would. If I was writing an SQL query I would just pull a cartesian product of items and dates, left join my transactions into that and slap a window function on it for my rolling balance. I really want to avoid having to export this to excel (it's for a client). Any ideas? Thanks!!!!!

I'm not sure this can be done easily. You have your case statements, you could always do an other case statement that does a similar check, but converts the week into the last day of the week and sums up all transactions before it.