As part of DS21 week on alteryx we had a lesson with Chris Love on using Multirow and multifield formulas, we had come across them briefly before but went into a lot more depth. He recommended Challenge 3, so i thought id give it a go even though it was classed as intermediate and so far i have just been doing beginner challenges.

The challenge was to calculate running averages for various different products

The running average were relatively simple to do using the multirow formula, it was just about getting the data into the right format so that I could use the multirow tool.

The first thing to do was to add a record ID, as you could then use this to pivot around in the transpose. There were some nulls in the value column so they were set to zero using the imputation tool. Then it was time to calculate the 3 and 6 month running averages

The key here was to set ‘Values for rows that dont exist’ to ‘Set to values of closest row’ which meant that the first few rows didnt return nulls. They were grouped by RM category and name so that each product had a separate average and wasn’t impacted by the product above

Both were then pivoted on record ID and a prefix was added to each to account for the different running average, and they could then be joined on the record ID. A final Transpose was used to make the data more readable.