Summing Across Columns With Changing Column Names

[Jon_ct]

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?

[grossal · Accepted solution]

Hi @Jon_ct,

you can do this using the Summarize and Join Tool.

Output (your sample data did not have all the rows needed to get your exact example output):

Workflow attached. Let me know if I got it right.

Best

Alex

[Jon_ct]

@grossal This worked perfectly!  Thanks for your response and for answering so quickly!