Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! 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()))

1 Solution

Accepted Solutions
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)

View solution in original post

11 Replies
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)

Not applicable
Author

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)

Not applicable
Author

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

Not applicable
Author

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

No Filter.PNGFilter.PNG

sunny_talwar

I was hoping if you can attach the qvf file

Uploading a Sample

Not applicable
Author

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.