Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
)
Thanks, for some reason the "NODISTINCT" it works on the sample data but giving odd numbers in the real data.
Could you post a sample QVW that is closer to your real setting?
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?