A rookie question on "SUM" in a Script:

[Anonymous]

I have an Excel file containing Invoice information including an "InvoiceDate".  I would like to get a SUM of the number of invoices issued for  each unique date.  I understand that this can be done at Script time. Using something I found in the Qlikview Community Discussions and trying to follow suit,  I have the following script:

[CustomerInvoice]:

LOAD [Customer Number],

          InvoiceNumber,

          InvoiceDate,

          day(InvoiceDate) as SaleDay,

          weekday(InvoiceDate) AS SaleWeekDay,

          month(InvoiceDate) AS SaleMonth,

          [Sales Amount]

[InvoiceCounts]:

LOAD distinct InvoiceDate, 1 as InvoiceCount

sum(InvoiceCount) as InvDateTotal

resident CustomerInvoice

But, I get a Script Error as follows:

Fieldnotfound-<InvoiceCount.

[InvoiceCounts]:

LOAD distinct InvoiceDate, 1 as InvoiceCount,

sum[InvoiceCount]as InvDateTotal

resident CustomerInvoice

[nagaiank]

Try the following script:

[InvoiceCounts]:

LOAD InvoiceDate, Count(Distinct InvoiceNumber) as InvDateTotal

resident CustomerInvoice Group By InvoiceDate;

[maxgro]

if possible post your excel

[CustomerInvoice]:

LOAD [Customer Number],

          InvoiceNumber,

          InvoiceDate,

          day(InvoiceDate) as SaleDay,

          weekday(InvoiceDate) AS SaleWeekDay,

          month(InvoiceDate) AS SaleMonth,

          [Sales Amount]

first problem is here: you need the excel file

to correct:

ctrl-e for editor

click button Table Files....

and follow instructions

to count distinct YourField by something Field1, Field2

load

  Field1, Field2,

  count (distinct YourField) as cnt

Resident

  YourPreviousLoadedTable

group by Field1, Field2;

[#4]

David,

1) I created a doc on the LOAD command. It may help you:

http://community.qlik.com/docs/DOC-5698

2) You sum in the script when you do not want to store the detail. QlikView will be able also to sum your data in the user interface. If you store the detail, you will be able to show the aggregation (total, count ...) and the detail also.

Fabrice