Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try the following script:
[InvoiceCounts]:
LOAD InvoiceDate, Count(Distinct InvoiceNumber) as InvDateTotal
resident CustomerInvoice Group By InvoiceDate;
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;
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