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.

Hi

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

Andy.

"nick_gardiner" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[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.

--
Regards

Roger Govier


<Andy> wrote in message news:[email protected]...
> Hi
>
> You could use COUNT on the day field, rather than the purchases.
>
> Andy.
>
> "nick_gardiner"
> <[email protected]> wrote in
> message
> news:[email protected]...
>>
>> 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
>>
>
>