• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table Sigma Help

L

lwilt

Member
I have a pivot table and with in my data for product sales I have each week that an order occurs. I would like to be able to show each product group by week the number of orders, total qty, and total sales for that week that occurred. If I do just the sales by week then the table looks perfect. When I try to bring in a summation of qty by week and number of orders by week with the sales by week it screws up the table. Any idea how to fix that?


thanks,

Logan
 
Can you post a short sample of what the data tables looks like (just need the relevant columns)? Since PivotTables are very dependent on data arrangement, this will let us better guide you on how to setup the PT. Thanks.
 
[pre]
Code: 
Week Number Product Group Order Number Qty Sales
31          4             SO743        3   45.98
31          4             SO899        18  109.52
31          5             SO122        4   62.31
30          2             SO312        1   31.50
[/pre]

So for each week I want to be able to count the number of orders, sum the qty, and sum the sales for at each product group level
 
To create the PT, first put Week Numbers into the Row Labels, then put Qty and Sales into Values area. Next, add Sales again to the Values area. It will initially show up as "Sum of Sales2" but you can change the field settings to be a count rather than a sum.


That should give you the table you initially described. If you need by product group, I'd either add the Product Group to the column labels, or as a Report Filter, depending on your preference.
 
lwilt


Bumping your post to keep it at the top is not the thing to do, others have questions, forum members have their lives out side of the forum, there could be many reasons you do not receive an answer in the time frame YOU think you should.


Bump post deleted
 
How do you sort it so that each of the 3 sigma values for every week are next to each other instead of have week 1-30 for the count, then week 1-30 for the sum of qty, then week 1-30 for the sum of sales?
 
FOllowing the steps I described, my report looks like this:

[pre]
Code: 
PivotTable below:
Data
Week Number	Sum of Qty	Sum of Sales	Count of Sales
30	            1	           31.5           	1
31	           25	           217.81           	3
Grand Total	   26	           249.31           	4
Is this how you want it to look? If so, and your table looks like this:

PivotTable below:
Week Number	Data	        Total
30	        Sum of Qty	1
Sum of Sales	31.5
Count of Sales	1
31	        Sum of Qty	25
Sum of Sales	217.81
Count of Sales	3
Total Sum of Qty		26
Total Sum of Sales		249.31
Total Count of Sales		4
[/pre]
You need to grab the cell/block with "Data" in it, and move it so that it is in the Row Labels area (e.g., where the word "Total" currently is. If you like the latter format, do the opposite.
 
I want to look like that except that the columns would be the count and sums for each week sorted by week number so that each week is grouped with itself
 
I'm afraid I'm not visualizing...could you post a sample of what you want it to look like

(manually constructed)?
 
[pre]
Code: 
Product Group   Week30 Orders Week30 Qty Week30 Sales Week31 Orders Week32 Qty
1               15            1000       15000        8             450
2               1             5          30           6             50
3               9             40         1000         2             2
[/pre]

Something like this.


Hope that helps.
 
Product Group goes in Row Labels

Week Number goes in Column Labels

Order Number, Qty, and Sales go into the Values area. Order Number will be a count, the other two will be a sum. Ends up like the following

[pre]
Code: 
PivotTable layout:
Week Number	Data
30			                                31
Product Group	Count of Order Number	Sum of Qty	Sum of Sales	Count of Order Number	Sum of Qty	Sum of Sales
2	        1                	1        	31.5
4			                                        	2	                21	        155.5
5				                                        1                	4	        62.31
Grand Total	1	                1	        31.5        	3	                25	        217.81
[/pre]
 
You must log in or register to reply here.
Back
Top