Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I wish to derive the cumulative column (preferably with expression) based on intially adding the Daily amount to the Value, then from the resulting figure subsequently add the Daily amount.
I need help desperately, thanks.
Date | Type | Value | Daily amount | Cumulative |
29/04/2022 | A | 128.61 | 32.00 | 256.61 |
28/04/2022 | A | 128.61 | 32.00 | 224.61 |
27/04/2022 | A | 128.61 | 32.00 | 192.61 |
26/04/2022 | A | 128.61 | 32.00 | 160.61 |
Hi,
You can use the following expressions:
Ascending order
RangeSum(Above(Sum([Daily amount]),0,RowNo()))+Sum(Value)
Descending order
RangeSum(Below(Sum([Daily amount]),0,NoOfRows())) + Sum(Value)
Please note that the order of columns is important. In your case Date should be in third place
Regards,
Vitalii
Hi,
You can use the following expressions:
Ascending order
RangeSum(Above(Sum([Daily amount]),0,RowNo()))+Sum(Value)
Descending order
RangeSum(Below(Sum([Daily amount]),0,NoOfRows())) + Sum(Value)
Please note that the order of columns is important. In your case Date should be in third place
Regards,
Vitalii
Hi @vchuprina ,
Thanks alot for that solution. Unfortunately I omitted field as such the result is no more desirable.
Could kindly assist me one more time with the below with the inclusion of the ID column.
Date | Type | ID | Value | Daily Amount | Cummulative |
29/04/2022 | A | LD2126334530 | 128.61 | 32.00 | 256.61 |
28/04/2022 | A | LD2126334530 | 128.61 | 32.00 | 224.61 |
27/04/2022 | A | LD2126334530 | 128.61 | 32.00 | 192.61 |
26/04/2022 | A | LD2126334530 | 128.61 | 32.00 | 160.61 |
Clearly, the expressions need some modifications.
Many thanks.
Hi,
Could you please provide an example of the the expected result, because I can't understand what is wrong
Regards,
Vitalii
Hi @vchuprina sorry about the miss up.
So I essentially copied the expression into my model and the result was bizarre. The intial logic still holds but with more columns.
Expected Result
Contract ID | Customer ID | Date | Value Date | Maturity Date | Accrued Interest (GL) | Daily Interest | Ascending order | Expected Result |
34530 | 100 | 29/04/2022 | 02/09/2021 | 02/09/2022 | 204.18 | 1.18 | 205.36 | 214.79 |
34530 | 100 | 28/04/2022 | 02/09/2021 | 02/09/2022 | 204.18 | 1.18 | 205.36 | 213.61 |
34530 | 100 | 27/04/2022 | 02/09/2021 | 02/09/2022 | 204.18 | 1.18 | 205.36 | 212.43 |
34530 | 100 | 26/04/2022 | 02/09/2021 | 02/09/2022 | 204.18 | 1.18 | 205.36 | 211.25 |
34530 | 100 | 25/04/2022 | 02/09/2021 | 02/09/2022 | 204.18 | 1.18 | 205.36 | 210.07 |
34530 | 100 | 24/04/2022 | 02/09/2021 | 02/09/2022 | 204.18 | 1.18 | 205.36 | 208.89 |
34530 | 100 | 23/04/2022 | 02/09/2021 | 02/09/2022 | 204.18 | 1.18 | 205.36 | 208.89 |
34530 | 100 | 22/04/2022 | 02/09/2021 | 02/09/2022 | 204.18 | 1.18 | 205.36 | 207.72 |
34530 | 100 | 21/04/2022 | 02/09/2021 | 02/09/2022 | 204.18 | 1.18 | 205.36 | 206.54 |
34530 | 100 | 20/04/2022 | 02/09/2021 | 02/09/2022 | 204.18 | 1.18 | 205.36 | 205.36 |
Hi @vchuprina apologies for the miss up.
So I essentially copied the expressions into my model and the results were bizzare. The logoc still holds but with multiple additional columns as depicted below.
Expected result highlighted in green.
Hi Peter,
You should change the priority of your columns on the sort tab, in your case Date should be the lowest dimension on the Priority List.
Example:
Date is the lowest dimension
Result:
ContactID is the lowest dimension
Regards,
Vitalii
Hi,
Please check the attached file.
Thank you kindly @vchuprina