topic Re: Accumulate data ignoring select dimension in QlikView

Accumulate data ignoring select dimension

Fri, 10 Mar 2017 11:15:28 GMT

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()))

Re: Accumulate data ignoring select dimension

sunny_talwar — Fri, 10 Mar 2017 12:31:38 GMT

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)

Re: Accumulate data ignoring select dimension

Fri, 10 Mar 2017 12:50:18 GMT

Thanks but it doesn't work I'm afraid

Re: Accumulate data ignoring select dimension

sunny_talwar — Fri, 10 Mar 2017 12:52:07 GMT

How about this:

RangeSum(Above(

, 0, RowNo()))

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

Re: Accumulate data ignoring select dimension

Fri, 10 Mar 2017 12:55:59 GMT

That doesn't work either

Re: Accumulate data ignoring select dimension

sunny_talwar — Fri, 10 Mar 2017 12:57:12 GMT

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

Re: Accumulate data ignoring select dimension

Fri, 10 Mar 2017 13:07:44 GMT

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

Re: Accumulate data ignoring select dimension

sunny_talwar — Fri, 10 Mar 2017 13:09:27 GMT

I was hoping if you can attach the qvf file

Uploading a Sample

Re: Accumulate data ignoring select dimension

Fri, 10 Mar 2017 15:24:19 GMT

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

Re: Accumulate data ignoring select dimension

sunny_talwar — Fri, 10 Mar 2017 15:27:47 GMT

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

Re: Accumulate data ignoring select dimension

Fri, 10 Mar 2017 15:40:42 GMT

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

Re: Accumulate data ignoring select dimension

sunny_talwar — Fri, 10 Mar 2017 16:42:33 GMT

Here you go...

When not selected

When Selected

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)