Pivot tables
 

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.


--
nick_gardiner
------------------------------------------------------------------------
nick_gardiner's Profile: http://www.excelforum.com/member.php...o&userid=35094
View this thread: http://www.excelforum.com/showthread...hreadid=548525

Pivot tables
 

Hi

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

Andy.

"nick_gardiner"
wrote in message
news:nick_gardiner.28xe9y_1149505203.9595@excelfor um-nospam.com...

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.


--
nick_gardiner
------------------------------------------------------------------------
nick_gardiner's Profile:
http://www.excelforum.com/member.php...o&userid=35094
View this thread: http://www.excelforum.com/showthread...hreadid=548525

Pivot tables
 

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.

--
Regards

Roger Govier


<Andy wrote in message ...
Hi

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

Andy.

"nick_gardiner"
wrote in
message
news:nick_gardiner.28xe9y_1149505203.9595@excelfor um-nospam.com...

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.


--
nick_gardiner
------------------------------------------------------------------------
nick_gardiner's Profile:
http://www.excelforum.com/member.php...o&userid=35094
View this thread:
http://www.excelforum.com/showthread...hreadid=548525