topic Re: Valuelist issue when using sum, aggr, above functions in QlikView

Valuelist issue when using sum, aggr, above functions

Fri, 07 Aug 2015 16:13:10 GMT

Hi,

I'm using Valuelist for "Cash Flow" data visualization in a Pivot Table. However, in the first row I needed to rollout the running balance over the Month dimension. So I had to use a combination of sum, aggr, rangesum, above functions to get it done:

sum(aggr(rangesum(above(total sum({<Month=>}Amount),0,RowNo())),Month))

The next row I needed to use that running amount -in the first row- to calculate something else like the ending balance. But when I tried to use the same expression it wasn't returning any value.

So here are my questions:

1. What could the reason for this behavior? Is there any work-around?

2. How can I use the value in the first row or any certain row to calculate something in a different row?

Please see the attached sample.

Re: Valuelist issue when using sum, aggr, above functions

swuehl — Fri, 07 Aug 2015 16:25:08 GMT

Try

pick(match(ValueList('acc1','acc2','acc3'),'acc1','acc2','acc3'),

sum(aggr(NODISTINCT rangesum(above(total sum({<Month=>}Amount),0,RowNo())),Month))

,sum(aggr(NODISTINCT rangesum(above(total sum({<Month=>}Amount),0,RowNo())),Month))+1

,sum(aggr(NODISTINCT rangesum(above(total sum({<Month=>}Amount),0,RowNo())),Month))+2

)

Re: Valuelist issue when using sum, aggr, above functions

Fri, 07 Aug 2015 16:48:39 GMT

Thanks, for some reason the "NODISTINCT" it works on the sample data but giving odd numbers in the real data.

Re: Valuelist issue when using sum, aggr, above functions

swuehl — Fri, 07 Aug 2015 17:24:32 GMT

Could you post a sample QVW that is closer to your real setting?

Re: Valuelist issue when using sum, aggr, above functions

Fri, 07 Aug 2015 19:23:10 GMT

Well, it seems due to data complexity issue, I can't use "NODISTINCT".

Is there anyway to assign that value into some kind of variable, array, or something and use it in other calculations within the same list?