Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Accumulate data ignoring select dimension

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.

DateValueAccumulated value (EOD balance)
1100100
2200300
3300600
44001,000

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

DateValueAccumulated value (EOD balance)EOD balance should be
3300300600
44007001,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()))

11 Replies
Not applicable
Author

I've attached some dummy data - will this work?

Edit: realised later you needed the expected output - I've added a second sheet with two scenarios: no filter and a filter on 7th and 8th Dec

sunny_talwar

Here you go...

When not selected

Capture.PNG

When Selected

Capture.PNG

Expression

RangeSum(Above(

Sum({$< [Segment 1] = {'0'},

  [Segment 2] = {'0'},

        [Segment 3] = {'999'},

        [Nominal Code] = {'7100'},

        OriginatingCurrency = {'USD'},

        LineRef -= {'Balance Brought Forward'},

        [TRX Date]>} ([OriginatingDebit]-[OriginatingCrebit])) , 0, RowNo()))

*

Avg({$< [Segment 1] = {'0'},

  [Segment 2] = {'0'},

        [Segment 3] = {'999'},

        [Nominal Code] = {'7100'},

        OriginatingCurrency = {'USD'},

        LineRef -= {'Balance Brought Forward'}>} 1)