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.
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.