Alteryx Designer Discussions

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

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

kiotsuresh
7 - Meteor

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.

5 REPLIES 5
Luke_C
16 - Nebula

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

 

 

kiotsuresh
7 - Meteor

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
Luke_C
16 - Nebula

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. 

Christina_H
12 - Quasar

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

Christina_H_0-1660742341338.png

 

kiotsuresh
7 - Meteor

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

Labels