topic Re: Cumulative Sum in script in QlikView

Cumulative Sum in script

crystles — Fri, 29 Jan 2016 16:01:58 GMT

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

Re: Cumulative Sum in script

sunny_talwar — Fri, 29 Jan 2016 16:06:25 GMT

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

Re: Cumulative Sum in script

crystles — Fri, 29 Jan 2016 16:07:51 GMT

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

Anonymous — Fri, 29 Jan 2016 16:14:09 GMT

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

settu_periasamy — Fri, 29 Jan 2016 16:17:33 GMT

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

Re: Cumulative Sum in script

crystles — Fri, 29 Jan 2016 16:20:40 GMT

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

Re: Cumulative Sum in script

sunny_talwar — Fri, 29 Jan 2016 16:38:55 GMT

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?