We are trying to pull some data regarding product renewals across the different service contracts.
The business goal is to identify how many products were renewed, taking in consideration the additional line items and contract end dates that are created each time the product is renewed.
I have created this table in Excel to demonstrate the required result:Service contract Item No. Product ID Contract Start Contract End Renewal (Y/N) For which year the product renewed? Renewal indicator 123 100 Taxservice 01.01.2020 01.01.2021 No, it's the first year of the contract 123 200 Payroll 01.01.2020 01.01.2021 No, it's the first year of the contract 123 300 Taxservice 01.01.2021 01.01.2022 Y 2022 1 123 400 Payroll 01.01.2021 01.01.2022 Y 2022 1 123 500 Taxservice 01.01.2022 01.01.2023 Y 2023 1
And then the renewal rate for each year would be:Product 2022 2023 Taxservice 100% 100% Payroll 100% 0%
Has anyone came across a similar analytic requirement or can point to build in renewal fields in S/4C that can simplify the logic?