Summarise Last X Columns of Data (Help!)

Question

Hey,

I have a dataset (attached) that I need to summarise the last 52 weeks worth of data each Monday. The data set would have one additional column with the new weeks worth of data therefore it needs to be dynamic / have a lookup function the previous week.

The weeks (columns) are represented by a lookup number, in chronological order 1,2,3,4 however are representative of actual weeks I have in a separate lookup table.

Effectively I need to each Monday run a query that both sums and counts the number of records greater than 0 from the last 52 weeks. In the case of the data attached, this would be column "891" back to column "840". If its of any help, the new weekly data will always be appended at the end of the data.

Any help would be much appreciated as I cant work this out!

Thanks!

Weekly Data.xlsx

OTrieger · Answer

You will be able to use Sample tool to create that logic for the last 52 weeks, by indicating to select the last 52 items, which should be the last 52 weeks. So work out your logic by field name or with sample tool, each will work fine

OTrieger · Answer

@JoeMapsIk

I'm not able to open the excel file and see the headers, but that is how I would do it.

Connect the input file to Field Info tool, that will give you all the headers.

Then create a logic to identify your last 52 weeks, that where the fields names come into play. Add with Formula and update the value for Description to "Need" to all the fields that you will need. Connect a Dynamic Rename tool, one anchor with the data and the second anchor with the mapped needed data. Select Update Description from the options. Then Connect Dynamic Select and set a formula Description = "Need".

Now you will be left only with the needed weeks.

Use Summarize tool to sum up and count the entries.