Tricky requirement in PivotTable: date problem in page field

[mrchonginhk]

I have to report average DAILY sales of certain products in Pivot Table, and my boss has make a must requriement for me to put in the page field a button for him so that he can select the view as Jan, Feb, mar... and Dec and of course ALL for the year. He is always interest to see the DAILY average sales for a particular month or for whole year not the monthly total or annual total.

So the data I gathered are under this column:-

Product, date, sales value

My initial attempt was to create a calculated field to divide sales by 31 to give the daily average. This works ONLY when he select Jan or Feb or.. Dec in page view. If he selects ALL, it is annual total sales divided by 31 which is meaningless.

Also not all months are 31 days.

How to tackle this extremely tricky questions ? I have used Pviot Table for years and I still cannot figure this out.

Any expert here can help me from getting fired ? He keeps asking me why I cannot do this "Simple" thing .... help !!!!!!!!!!!!!

ray:

 

[mrchonginhk]

Re: Tricky requirement in PivotTable: date problem in page f

I tried to use average instead of sum but problem jumps out:-

My data has many items which has values under "Product" and "dates" but got zero values under "Sales Values". Using Average will give a very low number as it is distorted by these zero entries. I cannot delete these rows because the real data is bigger (eg it has "Value 2" column and many times "Sales Value" has zero value but Value 2 has value and I have to keep it as I need to analyse "Sales Value" and "Value 2" together as well).

Also the 28, 30, 31 days problem is killing me..

help pls !!!

 

[tactps]

Something like this will allow you to work out the number of the days in the month (if that helps):

=DAY(DATE(YEAR(B11),MONTH(B11)+1,1)-1)

 

[mrchonginhk]

Re: Tricky requirement in PivotTable: date problem in page f

Thanks.... how and where to incorporate your magic formular into the pivot table ? Calculated field or I have to put it in the data first ??

 

[tactps]

Magic formula? I think not - others on this site would probably give you a shorter one. lol.

You could use a calculated field I guess, or just put it in the spreadsheet in a cell next to where the result is normally located.

Not my great area of expertise (as you can tell).

 

[mrchonginhk]

Re: Tricky requirement in PivotTable: date problem in page f

I think the bottleneck is computing the average sales based on the total sum sales value divided by number of days within the item selected in the page field (ie if Jan is select, divide by 31, Feb divided by 29 and ALL divided by 365) instead of average based on number of entries in the data.

But really I have no idea as to how this can be done...

 

[mrchonginhk]

Re: Tricky requirement in PivotTable: date problem in page f

Another possible solution is exluding zero entries when pivot table does the average calculation... any chance for this route ?

 

[Ahnold]

Re: Tricky requirement in PivotTable: date problem in page f

mrchonginhk,
Not sure if this helps. You can insert a new column and use this formula to get the month.

=TEXT(B2,"mmm")

If you are concerned about 0's one possible solution is to select your entire database and replace 0's with blank cells. Select the data then Ctrl + H, in "Find what" put in 0, in "Replace with" leave blank, check the box for "Find entire cells only" and select replace all. The pivot table will not calculate blank cells as part of the average equation.

Book3
	F	G	H	I	J	K
1	Date2	Jan
2
3	AverageofSales	Product
4	Date	A	B	C	D	GrandTotal
5	1/14/2004	185	200	560	278	361
6	1/15/2004	101		422	337	321
7	1/17/2004	796	628	315		580
8	1/18/2004				511	511
9	1/19/2004	30	290			203
10	1/21/2004		616			616
11	1/22/2004		567	159		431
12	1/23/2004		885		712	799
13	1/24/2004	419	379	12	616	357
14	1/25/2004	333		526	450	457
15	1/26/2004			395		395
16	1/27/2004			632		632
17	1/28/2004	283		123		229
18	1/29/2004	503	748	691	437	605
19	1/30/2004	143	281		563	329
20	1/31/2004	207	877			542
21	GrandTotal	305	544	422	489	438
Sheet2

 

[Ahnold]

Re: Tricky requirement in PivotTable: date problem in page f

This is the data layout:

Book3
	A	B	C	D
1	Product	Date	Date2	Sales
2	A	1/25/2004	Jan	333
3	D	5/11/2004	May	817
4	D	1/25/2004	Jan	759
5	C	3/2/2004	Mar	991
6	D	1/24/2004	Jan	616
7	A	4/7/2004	Apr
8	C	2/22/2004	Feb	63
9	A	2/28/2004	Feb	946
10	A	5/8/2004	May	165
11	B	3/6/2004	Mar	366
12	D	4/23/2004	Apr	259
13	B	2/12/2004	Feb	58
14	C	4/6/2004	Apr	761
15	B	1/31/2004	Jan	877
16	A	4/27/2004	Apr	654
17	C	5/10/2004	May	573
18	D	3/29/2004	Mar
19	A	3/1/2004	Mar	444
20	B	4/11/2004	Apr	748
21	B	3/16/2004	Mar	495
22	B	4/13/2004	Apr	303
23	B	2/15/2004	Feb	387
24	A	3/3/2004	Mar	354
25	D	4/12/2004	Apr	797
26	A	4/16/2004	Apr	60
Sheet2