I have Data like below, Date is is (MM/DD/YYY-Format)
Date | Sales | Item | Profit |
05/01/2022 | 100 | Monitor | 200 |
05/01/2022 | 200 | Mouse | 300 |
06/01/2022 | 300 | Monitor | 400 |
06/01/2022 | 400 | Mouse | 400 |
I want the output as below,
Date | Sales | Sales Previous Month | Item | Profit | Profit Previous Month |
05/01/2022 | 100 | April month value | Monitor | 200 | April month value |
05/01/2022 | 200 | April month value | Mouse | 300 | April month value |
06/01/2022 | 300 | 100 | Monitor | 400 | 200 |
06/01/2022 | 400 | 200 | Mouse | 400 | 300 |
Hi Community, Please Help me in achieving this.
Thank you.
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.
Instead the Data template I posted above consider the data template below, the solution required is same.
Date in MM/DD/YYYY format
Date | Datasource | Category Group | Category type | category Segment | Category type lvl1 | category type lvl2 | category type lvl3 | manu Year classification | Profit | Profit PM(new column) | Sales | SalesPM(New Column) | Revenue | Revenue PM(New Column) |
1/1/2020 | SQL | Furniture | Table | Amazon | Join | Type 1 | Type A | Year 2015 | 100 | December 2019 profit | 100 | December 2019 Sales | 100 | December 2019 Revenue |
1/1/2020 | SQL | Furniture | Chair | Wallmart | Join | Type 1 | Type A | Year 2015 | 200 | December 2019 profit | 200 | December 2019 Sales | 200 | December 2019 Revenue |
1/1/2020 | SQL | Furniture | Bookcase | Ebay | Join | Type 1 | Type A | Year 2015 | 300 | December 2019 profit | 300 | December 2019 Sales | 300 | December 2019 Revenue |
1/1/2020 | SQL | Furniture | Dining | Flipkart | Join | Type 1 | Type A | Year 2015 | 400 | December 2019 profit | 400 | December 2019 Sales | 400 | December 2019 Revenue |
2/1/2020 | SQL | Furniture | Table | Amazon | Union | Type 2 | Type C | Year 2016 | 500 | 100 | 500 | 100 | 500 | 100 |
2/1/2020 | SQL | Furniture | Chair | Wallmart | Union | Type 2 | Type C | Year 2016 | 600 | 200 | 600 | 200 | 600 | 200 |
2/1/2020 | SQL | Furniture | Bookcase | Ebay | Union | Type 2 | Type C | Year 2016 | 700 | 300 | 700 | 300 | 700 | 300 |
2/1/2020 | SQL | Furniture | Dining | Flipkart | Union | Type 2 | Type C | Year 2016 | 800 | 400 | 800 | 400 | 800 | 400 |
3/1/2020 | SQL | Furniture | Table | Amazon | Blend | Type 3 | Type D | Year 2017 | 900 | 500 | 900 | 500 | 900 | 500 |
3/1/2020 | SQL | Furniture | Chair | Wallmart | Blend | Type 3 | Type D | Year 2017 | 1000 | 600 | 1000 | 600 | 1000 | 600 |
3/1/2020 | SQL | Furniture | Bookcase | Ebay | Blend | Type 3 | Type D | Year 2017 | 1100 | 700 | 1100 | 700 | 1100 | 700 |
3/1/2020 | SQL | Furniture | Dining | Flipkart | Blend | Type 3 | Type D | Year 2017 | 1200 | 800 | 1200 | 800 | 1200 | 800 |
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.
Hey yeah tried your solution didn't worked out, Actually I can't share the workflow due to data reasons.