Hi,
I'm looking to understand the amount of product being delivered to me over a rolling time period.
The data comes in like this:
product date count
product 1 04/07 10
product 2 04/07 20
product 3 04/07 5
product 1 04/08 0
product 1 04/09 15
I've been able to use the crosstab tool so my data looks like this:
04/07 04/08 04/09
product 1 10 0 10
product 2 20 30 40
product 3 5 10 15
I would like to add a column that totals everything
04/07 04/08 04/09 Total
product 1 10 0 10 20
product 2 20 30 40 90
product 3 5 10 15 30
However I'd like to run this tomorrow and get
04/08 04/09 04/10 Total
product 1 0 10 5 15
product 2 30 40 0 70
product 3 10 15 20 45
The person requesting the data would like to see two tables as transposed above with the product coming within the week, and the product coming in 1+ weeks, and a sum of the type of product in each table.
How can I consistently sum when the column names will change daily?
