Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#REF in Sheets that refer to Pivot Tables | Excel Discussion (Misc queries) | |||
Refreshing Pivot Tables linked to Oracle Datasource | Excel Discussion (Misc queries) | |||
Dynamically changing several pivot tables at once | Excel Discussion (Misc queries) | |||
Ho to Delete "Ghost" Pivot Tables | Excel Discussion (Misc queries) | |||
Macro for Pivot Tables | Excel Discussion (Misc queries) |