Combining data from daily files to calculate average and max of UIDs

[Ksisterhen]

Hi-

there are reports created daily, stored in the same folder with a similar name, only the date changes. 

I am looking to have a workflow that combines the daily documents and averages the values in the 1st, 2nd, and 3rd columns if the bucket title matches. See example of two reports below and the export i am wanting.

Just example as there are 100s of daily files I am looking to combine and find the average of buckets from 

[griffinwelsh]

@Ksisterhensample is attached. You will need to update to match your folder paths.

1.Use a directory tool and feed into a filter to get the files for you day. My example is for dynamically picking today's date.

2.Read in files with a dynamic input tool

3.Use the summarize tool  to Group by buckets and average the columns.

4.Use a formula tool to generate your date tagged output path

5.Output your results taking the output path from the field we made in step 4

[Ksisterhen]

@griffinwelsh couple of questions

1. how do i add the tab name i want alteryx to pull data from?

2. why did you add the filter to say creation time = datetimetoday? i want to look at files created from the past 6 mths not just today

3. the column headers may change day over day how can i account for that?

[griffinwelsh]

@Ksisterhen updated workflow meeting your new requests is attached.

1. You can use a formula tool to append the sheet name to the fullpath. The format will be fullpath.xlsx|||sheetname

2. You did not specify the time period in your initial post. This was just an example to show how to use a filter to pick specific days.

3. This needed to be updated in the dynamic input to skip the headers and just pull in columns by position.

[Ksisterhen]

Thank you!

One other item i forgot to ask for, i am wanting to average the numbers from prior month and specific days, i/e today i want to see what the average was for the buckets from files dated the 13th of the prior months, i.e 7-13, 6-13, 5-13, etc, how can i add that in?

[griffinwelsh]

@Ksisterhen you will need to use a batch macro to accomplish this efficiently. Are you wanting one column for each day and the aggregates? Also are you wanting to identify the files by created date or filename date string?