topic Cumulative Sum - QlikView for QuickBooks in QlikView

Cumulative Sum - QlikView for QuickBooks

Wed, 25 Nov 2020 16:16:04 GMT

I'm using a version of QlikView that is an add-on to QuickBooks called Advanced Reporting, so I do not have control over the Import of the data. That being said, is there a way to properly create an Expression that cumulatively sums the values from another column.

Txn Type	Txn Date	Contract Term	Ref Number	Amount	Annual Amount	Running Total
Sales Order	03/22/2017	12	6953	$20,169.60	$20,169.60	$20,169.60
Sales Order	05/17/2017	1	7007	$499,856.50	$499,856.50	$499,856.50
Sales Order	05/17/2017	24	7001	$2,790,931.00	$1,395,465.50	$1,895,322.00

The Running Total column is supposed to cumulatively total the Annual Amount column, but doesn't work in the 2nd row. The Annual Amount column is derived from the Amount. This is the formula that I'm using to calculate the Running Total column: rangesum(above([Annual Amount],0,rowno(total)))

This is a Straight Table. Is my formula incorrect or is this not possible with a Straight Table? It needs to be sorted on Txn Date.

Thank you

Re: Cumulative Sum - QlikView for QuickBooks

sunny_talwar — Fri, 04 Aug 2017 17:29:18 GMT

Try this

RangeSum(Above(TOTAL [Annual Amount], 0, RowNo(TOTAL)))

Re: Cumulative Sum - QlikView for QuickBooks

sunny_talwar — Fri, 04 Aug 2017 17:31:22 GMT

And if this is a straight table, you can just use Full Accumulation on the Expressions tab with this as your expression

[Annual Amount]

Re: Cumulative Sum - QlikView for QuickBooks

Fri, 04 Aug 2017 17:44:05 GMT

Thanks, Sunny...your first solution worked although the second didn't seem to.