Hi ,
I Have Data Like below,
Item | Month | Name | Value |
Chair | Dec - 2021 | Balance | 100 |
Chair | Jan -2022 | Balance | 200 |
I need to calculate the number as average of current and prev month value, for jan 2022 (100+200/2=150) and same for all the available months. and I need to give a new name to that value as "Average Balance"
Output would be
item | Month | Name | Value |
Chair | Dec - 2021 | Average Balance | 100 |
Chair | Jan -2022 | Average Balance | 150 |
How to Achieve this using Alteryx. the data I'm Handling has lot of years data where this logic should work for all the months and year.
Solved! Go to Solution.
Hi @kiotsuresh
Do you need solution for only the sample input.
How you are expecting it should work for huge dataset.
Please share the logic for huge dataset, will post the solution.
Many thanks
Shanker V
Input:
Item | Month | Name | Value |
Chair | Dec-21 | Balance | 100 |
Chair | Jan-22 | Balance | 200 |
Chair | Feb-22 | Balance | 300 |
Chair | Mar-22 | Balance | 400 |
Chair | Apr-22 | Balance | 500 |
Chair | May-22 | Balance | 600 |
Chair | Jun-22 | Balance | 700 |
Chair | Jul-22 | Balance | 800 |
Chair | Aug-22 | Balance | 900 |
Chair | Sep-22 | Balance | 1000 |
Expected Output:
Item | Month | Name | Value | Logic |
Chair | Dec-21 | Average Balance | 100 | Average of Nov 21 and Dec 21 values |
Chair | Jan-22 | Average Balance | 150 | Average of Dec 21 and Jan 22 values |
Chair | Feb-22 | Average Balance | 250 | Average of Jan 22 and Feb 22 values |
Chair | Mar-22 | Average Balance | 350 | Average of Feb 22 and Mar 22 values |
Chair | Apr-22 | Average Balance | 450 | Average of Mar 22 and Apr 22 values |
Chair | May-22 | Average Balance | 550 | Average of Apr 22 and May 22 values |
Chair | Jun-22 | Average Balance | 650 | Average of May 22 and Jun 22 values |
Chair | Jul-22 | Average Balance | 750 | Average of Jun 22 and Jul 22 values |
Chair | Aug-22 | Average Balance | 850 | Average of Jul 22 and Aug 22 values |
Chair | Sep-22 | Average Balance | 950 | Average of Aug 22 and Sep 22 values |
Hi @kiotsuresh
Thanks for the detailed input.
Solution is simple. Like your previous query, need to use the multi field formula tool.
Posting the output for you below.
Many thanks
Shanker V
Step 1: Input
Step 2: Select
Step 3:
Step 4:
Step 5:
Step 6:
Kindly accept this solution if it provided a solution to your question.
Many thanks
Shanker V
@Felipe_Ribeir0 @ShankerV will this work for 2022-01-01 (yyyy-mm-dd) Format as well?
To answer you last question, the solution will work even if the date format is YYYY-MM-DD.
But one point to highlight,
If your incoming data's are random and not in order as per the input shared below. Please do the sort on Item as Ascending and Month as Ascending as it will bring the data in order.
If you do not sort, it will result in identifying the average between two different months.
Kindly accept this solution if it provided a solution to your question.
Many thanks
Shanker V