7 Replies Latest reply: Sep 25, 2017 4:38 AM by Antonio Mancini

# Calculate Closing Balance and Trend

Dear Experts,

I have below data which i want to calculate all on date selected. and also a trend for 2 dates selected.

Example Data:

Trdate                        Amount

01-JAN-2017                     10

02-JAN-2017                     10

03-JAN-2017                     10

04-JAN-2017                     10

05-JAN-2017                     10

06-JAN-2017                     10

07-JAN-2017                     10

Now if i select 06Jan2017 it should 60 (Sum of all previous records). and if i select 06 and 07-Jan2017 it should show trend:

06-JAN-2017      60

07-JAN-2017      70

Regards,

Zahid Rahim

• ###### Re: Calculate Closing Balance and Trend

You can use Cumulative sum in this case(Using RangeSum())

• ###### Re: Calculate Closing Balance and Trend

I want to use it with data filters. Can i have one example please.

• ###### Re: Calculate Closing Balance and Trend

may be this

RangeSum(above(sum(Amount),0,rowno()))

• ###### Re: Calculate Closing Balance and Trend

Dear arul

Thank you for the reply. your expression returns running total without any filter. Applying a filter only shows that current record value only.

Zahid Rahim

• ###### Re: Calculate Closing Balance and Trend

Hi,

try this

Sum(Aggr(RangeSum(Top(Sum({<Trdate>} Amount),1,RowNo())),Trdate))

Regards,

Antonio

• ###### Re: Calculate Closing Balance and Trend

It keep displaying zero.

• ###### Re: Calculate Closing Balance and Trend

LOAD Date(Date#(Trdate,'DD-MMM-YYYY'),'DD-MMM-YYYY') as Trdate,Amount Inline [
Trdate Amount
01-JAN-2017 10
02-JAN-2017 10
03-JAN-2017 10
04-JAN-2017 10
05-JAN-2017 10
06-JAN-2017 10
07-JAN-2017 10
]
(delimiter is
spaces);