This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.

Learn more
I AGREE
Reject

We have extended our Early Bird Tickets for Inspire 2023! Discounted pricing goes until February 24th. Save your spot!

community Data Science Portal 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 Discussions

Find answers, ask questions, and share expertise about Alteryx Designer 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
12 - Quasar
‎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
Labels