community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Allocation Logic in Alteryx

Hi ,

 

I have a data set for entire year, in which i have to allocate certain amount based on allocation logic of average of 3 months data, 6 months, 9 months and 12 months. Also this should roll forward. 

i.e. For Jan 3 months will be Oct, Nov and Dec, so on for 6 , 9 and 12 months respectively. For Feb it will be Nov, Dec and Jan.. So on.

 

Thanks !!

Pulsar

Here's the brute force approach with multi-row formulas. Not necessarily the most elegant!

 

running avg.png

Hi DavidP,

 

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.

 

Thanks!!

 

Test.PNG

Pulsar

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.

 

GL rolling avg.png

Thanks!! I will give it a try and will let you know if i have any further queries. 

Hi Daivd, 

 

 

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.

 

Thanks !!

Pulsar

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.

 

Hopefully this works for you

 

allocation.png

Labels