Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

crystles
Contributor III

Cumulative Sum in script

I have a table that lists Sales from Orders. It has many many variables, like Order Dates and Invoice Dates and other specifics about the Order and Shipping.

I need to get a cumulative sum of the items purchased by the Customer. However I can't seem to get it to work. There are thousands of records, but when I do a Sum(Sales) it outputs only like 4 Invoice Dates (key field) and 4 Sales amounts. When I do the Sum in the tables after loading the Script normally, it adds everything up correctly.

I'm not sure but I think I need RangeSum() and Peek() but I can't make them work.

Here's an Example

SalesTable:

Load

   CustomerID,

   Item,

   InvoiceDate,

   SalesAmt

From Sales.qvd;

SumSales:

Load

   CustomerID,

   Item,

   InvoiceDate,

   Sum(SalesAmt)

Resident SalesTable

Group By

   CustomerID,

   Item,

   InvoiceDate,

;

Drop Table SalesTable

Tags (1)
6 Replies

Re: Cumulative Sum in script

Can you share some data to test the code as well?

crystles
Contributor III

Re: Cumulative Sum in script

The data table is very large... over 2 million records. I don't think I can post all of that here

I can give examples of field names and such? To give you a better idea?

Re: Cumulative Sum in script

The script you have posted looks ok per se, so your issue could well in your expressions / variables / visualizations.

I have also got a feeling that you may be able what you want in the dashboard as opposed to the script.

For posting a sample qvw, this Blog Post Preparing examples for Upload - Reduction and Data Scrambling describes how reduce its size and scramble its data.

EDIT:  Except for the extra comma pointed out by Settu below.

Re: Cumulative Sum in script

Hi,

there is comma after Invoice date, Is that typo?

crystles wrote:

SumSales:

Load

   CustomerID,

   Item,

   InvoiceDate,

   Sum(SalesAmt)

Resident SalesTable

Group By

   CustomerID,

   Item,

   InvoiceDate,

;

Drop Table SalesTable

crystles
Contributor III

Re: Cumulative Sum in script

Yes sorry, that's a typo. I just wrote the example in the text box. No error checking, sorry.

Re: Cumulative Sum in script

Would it be possible to get data for 2 customers for 4-5 items and 4-5 months? May be like 20-30 rows of data per customer? Is that doable?

Community Browser