This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Thanks for the reply, let me ask you more specific question. Below screen shot is the sample of my data.I want to calculate the Average for 3, 6,9 and 12 months, for that particular GL, Exp type and Div Realign. how can i do this using multiple multi-row formulas.
In my 1st example I used a RecordID to define consecutive rows so that I can check if the previous 3,6,9 or 12 rows exist in order to calculate the average and if it didn't return NULL.
With varying GL codes, Exp types, etc, you can do the same thing with a Tile tool. In this case, I'm creating tiles based on unique GL codes and grouping by Exp Type and Div Realign. The Tile_SequenceNum then becomes the check if the previous 3,6,9 or 12 months' data exists.
This assumes, of course, that the rows are in the right order. If not, you have have to convert your year and month columns to a data and sort by GL Code and date.
The logic only checks if there are 3,6,9 or 12 consecutive rows of data in order to perform a calculation. October, for instance, is missing, so a 3 month avg of Nov,Sep,Aug might be incorrect. You'll have to decide how to handle that.
This make sense, thanks . But as you mentioned October, is missing, so a 3 month avg of Nov,Sep,Aug might be incorrect. Is there a way that if there is no data , Alteryx will take Zero value for that Month, GL, Div.
I've had a think about it. To accommodate missing months, you have to create the SequenceNum field with a multi-row formula and a proper date field built from the Year and Months fields rather than a Tile tool. That way, when you miss a month, it will increment by 2, if you miss 2 months increment by 3, etc. (I only allowed for missing up to 3 months)
You can then build logic in the multi-row formulas that calculate the averages. I'm sure my logic for those formulas isn't fool-proof for all scenarios, but it gives you the idea.
Another way to go is to insert rows with zero values for missing months, but I didn't explore that further.