So I have a table that is read into Qlik Sense for which I need to show the customer number, year, amount charged and renewal discount %.
What I am trying to do, is for each customer, show all of the future discount rates so that the overall discount given since whatever year you are on can be shown. Example below:
Cust No. | Year | Service Charge | Discount | Discount +1 | Discount +2 |
1 | 2015 | £34,586 | 0.0% | 3.0% | 2.2% |
1 | 2016 | £33,040 | 3.0% | 2.2% | 1.3% |
1 | 2017 | £38,919 | 2.2% | 1.3% | 2.4% |
1 | 2018 | £42,071 | 1.3% | 2.4% | 0.3% |
1 | 2019 | £41,694 | 2.4% | 0.3% | |
1 | 2020 | £42,307 | 0.3% | | |
2 | 2017 | £13,252 | 0.0% | 1.1% | 2.9% |
2 | 2018 | £17,836 | 1.1% | 2.9% | 1.6% |
2 | 2019 | £16,989 | 2.9% | 1.6% | |
2 | 2020 | £16,102 | 1.6% | | |
Here Discount +1 for customer 1 in 2015 is the value of the Discount in 2016 and so on.
I've tried the Peek and Previous functions, but these don't take into account when the customer changes. Can anyone help?