Pivot tables

[nick_gardiner]

I'm relatively new to pivot tables and would really appreciate some
help.

I have a list of sales data I am trying to analyse. I am looking to
measure the number of discrete sale days, in other words how many times
a customer visits per year. Customers only purchase once per day but
there are multiple items purchased and when using the date field with
Count function every item adds a count. Is there a way I can only count
unique days?

Thanks in advance.

 

[Guest]

Hi

You could use COUNT on the day field, rather than the purchases.

Andy.

"nick_gardiner" <[email protected]>
wrote in message
news:[email protected]...

 

[Roger Govier]

Hi

I don't think that will work Andy. I am assuming each transaction in the
source data contains Date, Customer, Item etc. hence there will be as
many Dates as there are Items purchased.

Assuming the source data is in its original transaction order, where all
purchases made are in strict order for each purchase made, then I would
add a further column to the source data.
Assuming Date is in Column A, Customer in B then add a new column called
Visits with the following formula
=IF(AND(A2=A1,B2=B1),0,1)
Copy down the Visit column for the extent of the data.

Now drag the Visits field to the Data area of the PT as a Sum value.