Pivot Table - Having column data move to proceeding columns

[steveg127]

Hello, new to the site so apologies if this has been solved before.

I am creating a pivot table that is going to be used as a 'back in stock' report that is showing the quantity specific products will be available. I am looking to have this show all proceeding weeks with said inventory once it is back in stock.

Is this possible?

(sorry I am on a work computer that isn't allowing xl2bb to be installed)

 

[MountainFog]

Can you provide a sample of the data used to generate this report?

 

[steveg127]

Can you provide a sample of the data used to generate this report?

Here you go -

SKU Code	Inventory (units) - netavailable	Inventory (units) - backorder	PO Units Open	Cumulative PO Units	Net Backordered	Is Backordered?	First Avail	Is Available?	Expected Receipt Date
100072134	0	11	7	7	-11	TRUE	-4	FALSE	7/27/2024
100072134	0	11	4	11	-11	TRUE	0	FALSE	8/10/2024
100109503	0	33	33	33	-33	TRUE	0	FALSE	5/30/2024
100109504	1	27	33	33	-26	TRUE	7	TRUE	5/30/2024
100127978	129	0	44	44	129	FALSE	173	TRUE	6/23/2024
100127978	129	0	182	226	129	FALSE	355	TRUE	8/19/2024
100127980	107	0	22	22	107	FALSE	129	TRUE	8/12/2024
100127981	636	0	150	150	636	FALSE	786	TRUE	6/23/2024
100127981	636	0	444	594	636	FALSE	1230	TRUE	8/12/2024
100127983	162	0	36	36	162	FALSE	198	TRUE	6/23/2024
100127983	162	0	86	122	162	FALSE	284	TRUE	8/19/2024
100127984	70	0	12	12	70	FALSE	82	TRUE	8/19/2024
100127985	0	8	12	12	-8	TRUE	4	TRUE	6/23/2024
100127985	0	8	24	36	-8	TRUE	28	TRUE	8/12/2024
100127991	75	0	29	29	75	FALSE	104	TRUE	6/19/2024
100127994	8	7	9	9	1	FALSE	10	TRUE	6/6/2024
100127994	8	7	3	12	1	FALSE	13	TRUE	8/20/2024
100127995	260	0	10	10	260	FALSE	270	TRUE	6/1/2024

The red text columns are calculated fields
Cumulative PO Units: =SUMIF(B$5:B5,B5,E$5:E5)
Net Backordered: =C5-D5
Is Backordered?: =G5<0
First Avail: =C5-D5+F5
Is available: =I5>0
Pivot Table Fields:

 

[MountainFog]

Should SKU 100127985 read 28 or 32 for period 8/12/2024 - 8/18/2024?

 

[steveg127]

28 - on first PO we are receiving 12 units (8 backorder) net 4.

 

[steveg127]

28 - on first PO we are receiving 12 units (8 backorder) net 4.

the next PO is for 24 so total of 28 will be in stock

 

[MountainFog]

Why does the 8 backordered repeat? Looking at your data it would make more sense to have the first line resolve the 8 backordered.

I would have the pivot table show a running table, create a new column in your chart reading Net Stock Impact:

=+[@[Inventory (units) - netavailable]]-[@[Inventory (units) - backorder]]+[@[PO Units Open]]

, and then have backordered units resolved with FIFO.

 

[steveg127]

Hello, I am pretty novice with excel so if this is a viable option I will definitely use this moving forward.

If I use this formula on the data is there a method to then continue with the initial question to have the pivot table data populate in proceeding columns?

 

[MountainFog]

Yes, but you will need to come up with new logic for your "IS BACKORDERED" column, due to the value of Inventory Units -Backorder is going to be 0 after the first shipment comes in.

Refresh your data after adding the column, and then drag Net Stock Impact to your values box in the PivotTable fields bar. Go to Value Field Settings > Show Values As tab > Running Total In