Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculating a Value By Taking Averages of current and previous months?

kiotsuresh
8 - Asteroid

Hi ,

 

I Have Data Like below,

 

ItemMonthNameValue
ChairDec - 2021Balance100
ChairJan -2022Balance200

 

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

itemMonthNameValue
ChairDec - 2021Average Balance100
ChairJan -2022Average Balance150

 

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.

10 REPLIES 10
ShankerV
17 - Castor

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

kiotsuresh
8 - Asteroid

@ShankerV 

Input:

ItemMonthNameValue
ChairDec-21Balance100
ChairJan-22Balance200
ChairFeb-22Balance300
ChairMar-22Balance400
ChairApr-22Balance500
ChairMay-22Balance600
ChairJun-22Balance700
ChairJul-22Balance800
ChairAug-22Balance900
ChairSep-22Balance1000

 

Expected Output:

ItemMonthNameValueLogic
ChairDec-21Average Balance100Average of Nov 21 and Dec 21 values
ChairJan-22Average Balance150Average of Dec 21 and Jan 22 values
ChairFeb-22Average Balance250Average of Jan 22 and Feb 22 values
ChairMar-22Average Balance350Average of Feb 22 and Mar 22 values
ChairApr-22Average Balance450Average of Mar 22 and Apr 22 values
ChairMay-22Average Balance550Average of Apr 22 and May 22 values
ChairJun-22Average Balance650Average of May 22 and Jun 22 values
ChairJul-22Average Balance750Average of Jun 22 and Jul 22 values
ChairAug-22Average Balance850Average of Jul 22 and Aug 22 values
ChairSep-22Average Balance950Average of Aug 22 and Sep 22 values
ShankerV
17 - Castor

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

 

 

Felipe_Ribeir0
16 - Nebula

Hi @kiotsuresh 

 

One way of doing this

 

Felipe_Ribeir0_0-1669042247407.png

 

 

ShankerV
17 - Castor

Hi @kiotsuresh 

 

Please find the solution below.

 

ShankerV_0-1669041622490.png

 

Many thanks

Shanker V

ShankerV
17 - Castor

Hi @kiotsuresh 

 

Input:

 

ShankerV_0-1669041718092.png

 

Output:

 

ShankerV_1-1669041737320.png

Many thanks

Shanker V

 

ShankerV
17 - Castor

@kiotsuresh 

 

Step 1: Input

Step 2: Select

 

ShankerV_0-1669041813071.png

 

 

Step 3:

ShankerV_1-1669041824586.png

 

Step 4:

ShankerV_2-1669041837240.png

 

Step 5:

 

ShankerV_3-1669041854569.png

 

Step 6:

 

ShankerV_4-1669041869862.png

 

 

Kindly accept this solution if it provided a solution to your question.

 

Many thanks

Shanker V

 

 

 

 

kiotsuresh
8 - Asteroid

@Felipe_Ribeir0 @ShankerV  will this work for 2022-01-01 (yyyy-mm-dd) Format as well?

ShankerV
17 - Castor

@kiotsuresh 

 

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

 

 

 

 

Labels