How to calculate last year's average to use in this year's projections

stephanie_cowan

I'm trying to calculate the average fee per event earned for the previous year and add that as a constant to a dataset to use to calculate projected fees for the upcoming quarter. I can calculate the constant by using Group By to get the average fee by year and then adding a filter formula to isolate the prior year but how do I join that to my original data set as a column? Or perhaps I'm going about this the wrong way and there is a better solution.

ColemenWilson

You'll need to join the single value to every row in the dataset. To do this, create a constant value, you could call it 'Join column 1' and it could be the number 1 or any other value, and include it in your group by. Then split out a new flow from WC WCB Activities and create a constant value column called something like 'Join column 2' that matches the value you created in 'Join column 1' and then join on Join Column 1 = Join column 2. Now you'll have that average value on every row of your data to compare with. If you need different values on different rows by year or month or whatever, then you would split out the dataflow as mentioned and join on year = year or year + month = year + month, etc…

ColemenWilson

You'll need to join the single value to every row in the dataset. To do this, create a constant value, you could call it 'Join column 1' and it could be the number 1 or any other value, and include it in your group by. Then split out a new flow from WC WCB Activities and create a constant value column called something like 'Join column 2' that matches the value you created in 'Join column 1' and then join on Join Column 1 = Join column 2. Now you'll have that average value on every row of your data to compare with. If you need different values on different rows by year or month or whatever, then you would split out the dataflow as mentioned and join on year = year or year + month = year + month, etc…