Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

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
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
17 - Castor

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
17 - Castor

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