Challenge:
Running Averages
The goal is to create a 3 and 6 month running averages for the values contained in columns:
c.LK98, p.LK98, c.1K, p.1k, c.NLP3, p.NLP3 by RM Category.
For example:
Month: Value: Running 3 mo. Average:
1 218 218
2 200 212
3 272 233
Hint: For Values that do not exist, set values to closest valid row.
Tools Used:
Transpose, Unique, Append, Formula, Union, Summarize, Sorting, Multi-Row Formula, Cross Tab, Dynamic Rename, Multi Join
Solution Steps:
This solution is a bit long as there are multiple requirements.
Steps:
1. Take the name, category, and year/month in a separate stream and take unique values of these 3 columns.
2. Append these 3 columns and use the formula tool to create 0 values. This step creates dummy 0 values for all the names and categories for all months and years. This will be useful in using values in the multi-row formula and in transpose as well.
3. Union step 2 result with the original data stream.
4. Use the Summarize tool to calculate the sum of all the values by grouping on RM Category, Year, Month, and Name.
5. Sort these items in the same order. RM Category, Year, Month, and Name.
6. Now, to create 3 months and 6 months running average, use the multi-row formula tool. Create a new field named 3Month and use the expression: ([Value]+[Row-1:Value]+[Row-2:Value])/3 Group this by Category and Name.
This formula is taking 3-row values and calculating the running average every 3 rows. (Choose Num Rows=3).
7. Similarly, to create 6 months average, use another Multi-row formula tool and use this expression: ([Value]+[Row-1:Value]+[Row-2:Value]+[Row-3:Value]+[Row-4:Value]+[Row-5:Value])/6. (Choose Num Rows=6).
6. Rename the columns from these 3 results with a prefix "3Mon_" & "6_Mon" respectively, so that these could be identified separately.
7. Use multi join to join the 3 month & 6 month average with the original data. Join condition would be on Category, Year, and Month.
8. Use the Transpose tool to transpose the data of 3-month and 6-month average in one column. The configuration will be - Select Category, Year, and Month as Key Columns. All other columns as Data Columns.
This output is the result required.
Check the solution here.
Post a Comment