community Alteryx IO Alteryx.com
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Alteryx Designer Desktop Discussions

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

Creating A new Column with the previous month value from the different column for all rows

kiotsuresh
8 - Asteroid
‎08-09-2022 10:05 AM

I have Data like below, Date is is (MM/DD/YYY-Format)

DateSalesItemProfit
05/01/2022100Monitor200
05/01/2022200Mouse300
06/01/2022300Monitor400
06/01/2022400Mouse 400

 

I want the output as below,

DateSalesSales Previous MonthItemProfitProfit Previous Month
05/01/2022100April month valueMonitor200April month value
05/01/2022200April month valueMouse300April month value
06/01/2022300100Monitor400200
06/01/2022400200Mouse 400300

 

Hi Community, Please Help me in achieving this.

 

Thank you.

Reply
0 Likes
5 REPLIES 5
Luke_C
17 - Castor
‎08-09-2022 10:37 AM

Hi @kiotsuresh 

 

You could use the multi-row formula tool to achieve this. Here's an example:

 

I converted the date to ensure that the data is always sorted properly. Then it's a simple matter of taking the row-1 value for each item using the multi-row formula's group-by function.

Luke_C_0-1660066625219.png

 

 

New Workflow4.yxmd
Reply
kiotsuresh
8 - Asteroid
‎08-17-2022 05:22 AM

Instead the Data template I posted above consider the data template below, the solution required is same.

 

Date in MM/DD/YYYY format

 

DateDatasourceCategory GroupCategory typecategory SegmentCategory type lvl1category type lvl2category type lvl3manu Year classificationProfitProfit PM(new column)SalesSalesPM(New Column)RevenueRevenue PM(New Column)
1/1/2020SQLFurnitureTableAmazonJoinType 1Type AYear 2015100December 2019 profit100December 2019 Sales100December 2019 Revenue
1/1/2020SQLFurnitureChairWallmartJoinType 1Type AYear 2015200December 2019 profit200December 2019 Sales200December 2019 Revenue
1/1/2020SQLFurnitureBookcaseEbayJoinType 1Type AYear 2015300December 2019 profit300December 2019 Sales300December 2019 Revenue
1/1/2020SQLFurnitureDiningFlipkartJoinType 1Type AYear 2015400December 2019 profit400December 2019 Sales400December 2019 Revenue
2/1/2020SQLFurnitureTableAmazonUnionType 2Type CYear 2016500100500100500100
2/1/2020SQLFurnitureChairWallmartUnionType 2Type CYear 2016600200600200600200
2/1/2020SQLFurnitureBookcaseEbayUnionType 2Type CYear 2016700300700300700300
2/1/2020SQLFurnitureDiningFlipkartUnionType 2Type CYear 2016800400800400800400
3/1/2020SQLFurnitureTableAmazonBlendType 3Type DYear 2017900500900500900500
3/1/2020SQLFurnitureChairWallmartBlendType 3Type DYear 2017100060010006001000600
3/1/2020SQLFurnitureBookcaseEbayBlendType 3Type DYear 2017110070011007001100700
3/1/2020SQLFurnitureDiningFlipkartBlendType 3Type DYear 2017120080012008001200800
Reply
0 Likes
Luke_C
17 - Castor
‎08-17-2022 06:13 AM

Hi @kiotsuresh 

 

Have you tried the solution I posted? I believe it should work (or be easily adaptable) in your workflow. Can you share your workflow and I can help you troubleshoot? That will help you best learn. 

Reply
Christina_H
14 - Magnetar
‎08-17-2022 06:19 AM

Here's an alternative version using a join rather than multi-row formula, applied to both data sets.

Christina_H_0-1660742341338.png

 

Prev month values.yxmd
Reply
0 Likes
kiotsuresh
8 - Asteroid
‎08-17-2022 06:24 AM

Hey yeah tried your solution didn't worked out, Actually I can't share the workflow due to data reasons.

Reply
0 Likes
learn more about the python upgrade coming in may 2024
Labels