topic Running Total In A Pivot Table in QlikView

Running Total In A Pivot Table

pkelly — Thu, 08 Jul 2010 16:53:24 GMT

Appreciate that this is a quetsion which has been posted before but I have tried the sugestions with no success...

I have two tables namely SalesInvoice (multiple records per invoice) and Customer (one record per customer).

I have created a pivot table with the dimensions Customer and Customer Category.

My expressions are...

Sales

=sum(if(sin_FinancialYear = varCustomerSort, sin_Sales))

RowNo

=RowNo()

** All of my row numbers are being reported as 1 (I have included this as previous posts have suggested using this field).

The expression I would like to add is "Running Total"...

Can anyone please give me some suggestions on how to achieve this?

Many Thanks

Paul

Running Total In A Pivot Table

pkelly — Thu, 08 Jul 2010 21:04:25 GMT

Anyone any ideas?

Running Total In A Pivot Table

pkelly — Fri, 09 Jul 2010 11:37:59 GMT

Can anyone help me on this one?

Running Total In A Pivot Table

Fri, 09 Jul 2010 11:55:30 GMT

<body>Hi Im still getting to grips with QV in genral including scripting. I have come from primarily working with MS Access on large integrated and automated databases for reporting purposes. Im not sure that this will be specifically relevant, however I approached the same subject in MS Access some time ago and hope it might throw some light on an alternative viewpoint, "we dont always see the wood for the trees". It works on the basis of self referencing a table to produce this result:- <table border="1" bgcolor="#ffffff" cellspacing="0"> <caption>CUMUL_SUM_QUERY</caption><thead> <tr> <th bgcolor="#c0c0c0">ID</th><th bgcolor="#c0c0c0">VALUE</th><th bgcolor="#c0c0c0">CUMUL</th> </tr> </thead> <tbody> <tr valign="top"> <td align="right">1</td> <td align="right">10</td> <td align="right">10</td> </tr> <tr valign="top"> <td align="right">2</td> <td align="right">10</td> <td align="right">20</td> </tr> <tr valign="top"> <td align="right">3</td> <td align="right">10</td> <td align="right">30</td> </tr> <tr valign="top"> <td align="right">4</td> <td align="right">10</td> <td align="right">40</td> </tr> <tr valign="top"> <td align="right">5</td> <td align="right">10</td> <td align="right">50</td> </tr> <tr valign="top"> <td align="right">6</td> <td align="right">10</td> <td align="right">60</td> </tr> <tr valign="top"> <td align="right">7</td> <td align="right">10</td> <td align="right">70</td> </tr> <tr valign="top"> <td align="right">8</td> <td align="right">10</td> <td align="right">80</td> </tr> <tr valign="top"> <td align="right">9</td> <td align="right">10</td> <td align="right">90</td> </tr> <tr valign="top"> <td align="right">10</td> <td align="right">10</td> <td align="right">100</td> </tr> <tr valign="top"> <td align="right">12</td> <td align="right">10</td> <td align="right">110</td> </tr> <tr valign="top"> <td align="right">13</td> <td align="right">10</td> <td align="right">120</td> </tr> <tr valign="top"> <td align="right">14</td> <td align="right">10</td> <td align="right">130</td> </tr> <tr valign="top"> <td align="right">15</td> <td align="right">10</td> <td align="right">140</td> </tr> </tbody> <tfoot></tfoot> </table> The table TEST is as above with only the Fields ID and VALUE. SELECT TEST.ID, TEST.VALUE, Sum(TEST_1.VALUE) AS CUMUL FROM TEST, TEST AS TEST_1 WHERE (((TEST_1.ID)<=[TEST].[ID])) GROUP BY TEST.ID, TEST.VALUE; You can also expand on this replacing ID as an unordered date field too. Reagrds J</body>

Running Total In A Pivot Table

pkelly — Mon, 12 Jul 2010 11:00:24 GMT

Thanks J...

I don't think that this will work for me as this looks like something I would have to do when I am loading the data via a script.

I am looking to do this on a pivot table so that when the user filters the running total will change.

Regards

Paul

Running Total In A Pivot Table

pkelly — Mon, 12 Jul 2010 14:54:48 GMT

Found what I was doing wrong....

The posts which had been looking at referred to Pivot Tables.

Suggestions included using full accumulation and if(rowno(total)=0,0,numsum(column(2),above(total column(3)))).

For me, full acummulation was not available to me and the formula did not work.

However, when i changed my table from a Pivot Table to a Straight Table the formula worked.

Regards

Paul

Re: Running Total In A Pivot Table

Anonymous — Fri, 10 Jun 2011 12:57:45 GMT

This works for me:

rangesum(above(column(1),0,rowno())) where column 1 contains the values I want to cumulate