Sum cumulatively after a condition

[BrunPierre]

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

[vchuprina · Accepted solution]

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

[BrunPierre]

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.

[vchuprina]

Hi, 

Could you please provide an example of the the expected result, because I can't understand what is wrong

Regards,

Vitalii

[BrunPierre]

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

[BrunPierre]

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.

[vchuprina]

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

[vchuprina]

Hi, 

Please check the attached file.

[BrunPierre]

Thank you kindly @vchuprina