Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you share some data to test the code as well?
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?
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.
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
Yes sorry, that's a typo. I just wrote the example in the text box. No error checking, sorry.
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?