Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
pkelly
Specialist
Specialist
‎2010-07-08 12:53 PM

Running Total In A Pivot Table

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

10,953 Views
0 Likes
1 Solution

Accepted Solutions
pkelly
Specialist
Specialist
‎2010-07-12 10:54 AM
Author

In response to pkelly

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

View solution in original post

837 Views
0 Likes
6 Replies
pkelly
Specialist
Specialist
‎2010-07-08 05:04 PM
Author

Anyone any ideas?

837 Views
0 Likes
pkelly
Specialist
Specialist
‎2010-07-09 07:37 AM
Author

In response to pkelly

Can anyone help me on this one?

837 Views
0 Likes
Not applicable
‎2010-07-09 07:55 AM

In response to pkelly

<body><p>Hi</p> <p>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. </p> <p>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, &quot;we dont always see the wood for the trees&quot;.  </p> <p>It works on the basis of self referencing a table to produce this result:-</p> <p> <table border="1" bgcolor="#ffffff" cellspacing="0"> <caption><b>CUMUL_SUM_QUERY</b></caption></span><thead> <tr> <th bgcolor="#c0c0c0">ID</span></th><th bgcolor="#c0c0c0">VALUE</span></th><th bgcolor="#c0c0c0">CUMUL</span></th> </tr> </thead> <tbody> <tr valign="top"> <td align="right">1</span></td> <td align="right">10</span></td> <td align="right">10</span></td> </tr> <tr valign="top"> <td align="right">2</span></td> <td align="right">10</span></td> <td align="right">20</span></td> </tr> <tr valign="top"> <td align="right">3</span></td> <td align="right">10</span></td> <td align="right">30</span></td> </tr> <tr valign="top"> <td align="right">4</span></td> <td align="right">10</span></td> <td align="right">40</span></td> </tr> <tr valign="top"> <td align="right">5</span></td> <td align="right">10</span></td> <td align="right">50</span></td> </tr> <tr valign="top"> <td align="right">6</span></td> <td align="right">10</span></td> <td align="right">60</span></td> </tr> <tr valign="top"> <td align="right">7</span></td> <td align="right">10</span></td> <td align="right">70</span></td> </tr> <tr valign="top"> <td align="right">8</span></td> <td align="right">10</span></td> <td align="right">80</span></td> </tr> <tr valign="top"> <td align="right">9</span></td> <td align="right">10</span></td> <td align="right">90</span></td> </tr> <tr valign="top"> <td align="right">10</span></td> <td align="right">10</span></td> <td align="right">100</span></td> </tr> <tr valign="top"> <td align="right">12</span></td> <td align="right">10</span></td> <td align="right">110</span></td> </tr> <tr valign="top"> <td align="right">13</span></td> <td align="right">10</span></td> <td align="right">120</span></td> </tr> <tr valign="top"> <td align="right">14</span></td> <td align="right">10</span></td> <td align="right">130</span></td> </tr> <tr valign="top"> <td align="right">15</span></td> <td align="right">10</span></td> <td align="right">140</span></td> </tr> </tbody> <tfoot></tfoot> </table> </p> <p>The table TEST is as above with only the Fields ID and VALUE. </p> <p>SELECT <br />TEST.ID, <br />TEST.VALUE, <br />Sum(TEST_1.VALUE) AS CUMUL</p> <p>FROM TEST, TEST AS TEST_1</p> <p>WHERE (((TEST_1.ID)&lt;=[TEST].[ID]))</p> <p>GROUP BY TEST.ID, TEST.VALUE;</p> <p>You can also expand on this replacing ID as an unordered date field too. </p> <p>Reagrds</p> <p>J</p></body>

837 Views
0 Likes
pkelly
Specialist
Specialist
‎2010-07-12 07:00 AM
Author

In response to

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

837 Views
0 Likes
pkelly
Specialist
Specialist
‎2010-07-12 10:54 AM
Author

In response to pkelly

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

838 Views
0 Likes
Anonymous
Not applicable
‎2011-06-10 08:57 AM

In response to pkelly

This works for me:

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

837 Views
0 Likes