Moving Avg and Median for multiple columns

I need to calculate average and median by fiscal year and month for the columns C, D, E, F. The values in C, D, E, F represent time in days for each row falling in a specific month and year.

Based on Sample Data attached, I need output as:

Row Year Month Avg_C Median_C YTD_Avg_C YTD_Median_C ...

1 FY 2012 02

2 FY 2012 03

For the month 02 : Avg_C and YTD_Avg_C will be the same since it is only month 02 into consideration

For the month 03: Avg C will be only average for rows that fall in the month 03 and YTD Avg C will be average for the rows that fall in month 02 and month 03. I believe this is somewhat easier to achieve with some summarize tool and runner tool, but I haven't gotten around to create it yet. I was hoping to combine all of the calculations together.

Median is where I am requesting help. Median C will be only for month 03 whereas YTD Median C will be median value for both months.

Any help is appreciated. Hope I was able to describe my request clearly.

I was trying to see if this could help, but I couldn't replicate the steps.

https://community.alteryx.com/t5/Alteryx-Designer/Moving-Median/m-p/439015

Sample_Data.xlsx

Developer

Developer Tools

Macros

Accepted answers

Yoshiro_Fujimori

Hi @pappoppuv ,

I created a new workflow from scratch, so that the newly added value columns do not affect the process.

I did some simple check and looks OK.

Please verify the output by yourself and let me now if you find any issue.

Workflow

Output at the 1st Browse tool

(Personally I prefer this portrait style than landsccape style at the 2nd Brows tool)

Output at the 2nd Brows tool

YTD_Avg_Median_2.yxzp

All comments

Yoshiro_Fujimori

Hi @pappoppuv ,

I tried to achieve this with Batch Macro.

With a little test, it seems to be correct, but please check by yourself.

Main workflow

Batch Macro

Output

YTD_Avg_Median.yxzp

pappoppuv

I changed the attached sample to my workflow inputs, there are multiple years of data that I am working with. After making the changes, the output is missing months 1,10, 11. Month 12 is showing up. Not sure if I missed something.

Yoshiro_Fujimori

Hi @pappoppuv ,

If you can share the data which is causing a problem, I may be able to help.

pappoppuv

I added more records. It is similar data for various months and years.

Sample_Data.xlsx

Yoshiro_Fujimori

Hi @pappoppuv ,

I pluged in your new data to the workflow and it seems to work on my Designer.

You see the month of 2012/10, 2012/11, 2013/01.

2012/01 is not on the Input Data. So it should be OK.

YTD_Avg_Median.yxzp

pappoppuv

I am not sure what I am doing wrong. All i am trying to do is change the column names, add two more columns that I need avg and median for. As soon as I change the column names, nothing seems to work. Can you please take a look and let me know where I am going wrong? I am trying to understand macros and how to better utilize them.

Your solution is working when I use my own data in excel format and the column names changed to C,D,E,F,G,H. If I connect it to database and the names are changed it doesnt work.

YTD_Avg_Median - Copy.yxzp

Yoshiro_Fujimori

@pappoppuv

I did not seriously consider about the scalability of this requirement.

Please allow me some time so that I can refactor the workflow to accept additional columns for Average and Median.

pappoppuv

It is working when I changed my datasource. I am still testing the data but I believe it is doing what I am looking for. Thank you for all the time and effort. Appreciate your help.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703