Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
We are struggling to find a solution for our problem, and hope one off you can help.
In our project we need to properly calculate the 'Amount to be paid' within user based time selections.
Based on the table below (see below signature), I have created several use cases for better understanding.
Use case 1: The user makes a time selection of 1-1-2015 up to and including 1-3-2015
- In this specific use case we expect that the most recent 'amount to be paid' (the -100 of 1-3-2015) is summed with the 'amounts to be paid' of the remaining records in the selection (-100 of 1-1-2015 and -100 of 1-2-2015), leading to a result of -300.
Use case 2: The user makes a time selection of 1-1-2015 up to and including 1-6-2015
- In this specific use case we expect that the most recent 'amount to be paid' (the 900 of 1-6-2015 ) is summed with the total sum of 'amount to be paid' before the contract renewal date in the given selection (-100 + -100 + -100), leading to an overall result of 600 (= 900 + -300).
Use case 3: The user makes a time selection of 1-1-2015 up to and including 31-12-2015
- In this specific use case we expect that the most recent 'amount to be paid' (the 300 of 1-12-2015 ) is summed with the total sum of 'amount to be paid' before the contract renewal date in the given selection (-100 + -100 + -100), leading to an overall result of 0 (= 300 + -300).
Use case 4: The user makes a time selection of 1-2-2015 up to and including 31-12-2015
- In this specific use case we expect that the most recent 'amount to be paid' (the 300 of 1-12-2015 ) is summed with the total sum of 'amount to be paid' before the contract renewal date in the given selection -100 + -100), leading to an overall result of 100 (= 300 + -200).
Use case 5: The user makes a time selection of 1-5-2015 up to and including 31-5-2015
- In this specific use case we expect that the most recent 'amount to be paid' (the1000 of 1-5-2015 ) is the result the selection
Use case 6: The user makes a time selection of 1-5-2015 up to and including 31-6-2015
- In this specific use case we expect that the most recent 'amount to be paid' (the 900 of 1-6-2015 ) is the result the selection
Kind regards,
Arjan IJlenhave
Date renewal contract | 1-4-2015 | |
Date | Amount paid | Amount to be paid |
1-1-2015 | 100 | -100 |
1-2-2015 | 100 | -100 |
1-3-2015 | 100 | -100 |
1-4-2015 | 100 | 1100 |
1-5-2015 | 100 | 1000 |
1-6-2015 | 100 | 900 |
1-7-2015 | 100 | 800 |
1-8-2015 | 100 | 700 |
1-9-2015 | 100 | 600 |
1-10-2015 | 100 | 500 |
1-11-2015 | 100 | 400 |
1-12-2015 | 100 | 300 |
Maybe something like
=Rangesum(
If(Max(Date) >= '1-4-2015',FirstSortedValue([Amount to be paid], -Date)),
Sum({<Date *= {"<1-4-2015"}>} [Amount to be paid])
)
Where constant '1-4-2015' is your renewal date and can be replaced with a variable or data record lookup.
Another option with a single Sum():
= Sum({<Date = (Date*{"<1-4-2015"})+{"$(=Date(Max(Date)))"}>} [Amount to be paid])