Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

Re: Accumulate data ignoring select dimension

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
Highlighted

Re: Accumulate data ignoring select dimension

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)

Highlighted
Not applicable

Re: Accumulate data ignoring select dimension

Thanks but it doesn't work I'm afraid

Highlighted

Re: Accumulate data ignoring select dimension

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)

Highlighted
Not applicable

Re: Accumulate data ignoring select dimension

That doesn't work either

Highlighted

Re: Accumulate data ignoring select dimension

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

Highlighted
Not applicable

Re: Accumulate data ignoring select dimension

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

Highlighted

Re: Accumulate data ignoring select dimension

I was hoping if you can attach the qvf file

Uploading a Sample

Highlighted
Not applicable

Re: Accumulate data ignoring select dimension

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

Highlighted

Re: Accumulate data ignoring select dimension

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