Accumulate data ignoring select dimension

[#1]

Hello,

I am trying to make a graph and a table which shows at any date what the EOD accumulated balance is. I am able to make the data accumulate but only when all dates are visible.

E.g. When no filter is on the date dimension the EOD balances are correct.

Date	Value	Accumulated value (EOD balance)
1	100	100
2	200	300
3	300	600
4	400	1,000

However when I filter to dates 3 and 4 the accumulation starts from 0.

Date	Value	Accumulated value (EOD balance)	EOD balance should be
3	300	300	600
4	400	700	1,000

The syntax I am using is the following

rangesum( above(

sum({$<[Segment 1]={'00'}, [Segment 2]={'00'}, [Segment 3]={'999'}, [Nominal Code]={'7100'}, OriginatingCurrency={'USD'}, LineRef -= {'Balance Brought Forward'}>}([OriginatingDebit]-[OriginatingCredit])/1000000)

,0,rowno()))

[sunny_talwar]

Try this

RangeSum(Above(

Sum({$<[Segment 1] = {'00'}, [Segment 2] = {'00'}, [Segment 3] = {'999'}, [Nominal Code]  ={'7100'}, OriginatingCurrency ={'USD'}, LineRef -= {'Balance Brought Forward'}, Date>} ([OriginatingDebit]-[OriginatingCredit])/1000000)

, 0, RowNo())) * Avg(1)

[#3]

Thanks but it doesn't work I'm afraid

[sunny_talwar]

How about this:

RangeSum(Above(

Sum({$<[Segment 1] = {'00'}, [Segment 2] = {'00'}, [Segment 3] = {'999'}, [Nominal Code]  ={'7100'}, OriginatingCurrency ={'USD'}, LineRef -= {'Balance Brought Forward'}, Date>} ([OriginatingDebit]-[OriginatingCredit])/1000000)

, 0, RowNo()))

*

Avg({$<[Segment 1] = {'00'}, [Segment 2] = {'00'}, [Segment 3] = {'999'}, [Nominal Code]  ={'7100'}, OriginatingCurrency ={'USD'}, LineRef -= {'Balance Brought Forward'}>} 1)

[#5]

That doesn't work either

[sunny_talwar]

Would you be able to share a sample to check this out? I think it should work, but I might be missing a small piece

[#7]

Do you mean sharing screenshots? Let me know if you need anything else but I've attached an example of without and with filter.

• Column 1: TRX Date
• Col 2: Movement
• Col 3: Accumulated balance

[sunny_talwar]

I was hoping if you can attach the qvf file

Uploading a Sample

[#9]

I'm sorry, I can't share this because I don't have admin access. Is there anything else that would be helpful?

[sunny_talwar]

May be the raw data behind the app or may be share sample dummy data with the expected output.