Hi Kaushik, thank you for your reply. Some data is attached here.
I would like to eliminate all guests with an occurrence of only 1 and to
sum (or count) the remaining.
e.g. if Paul has 3 occurrences, Russell and 2 Occurrences and Steve has 1
By 1st eliminating Steve who has only 1 occurrence the sum would be 5 and
the count would be 2.
Thanks for your help, really appreciated.
On Tue, Aug 30, 2016 at 4:59 PM, Kaushik Solanki <firstname.lastname@example.org>
With your data and this script, this will be your output:
Data: LOAD Date, MonthName(Date) as MonthName, RowNo() as RowNum, Time, BillNo, Total, [Guest Name] FROM [Data for Occurance Question.xlsx] (ooxml, embedded labels, table is Sheet1); Occurences: LEFT JOIN (Data) LOAD MonthName, COUNT(DISTINCT RowNum) as Occurrence, [Guest Name] RESIDENT Data GROUP BY MonthName, [Guest Name]; FinalData: LOAD MonthName, COUNT(DISTINCT RowNum) as Times, COUNT(DISTINCT [Guest Name]) as Customers RESIDENT Data WHERE Occurrence > 1 GROUP BY MonthName;