Pivot Table Sum on Value Range

[rudypoochris]

Hi Guys,

This one is probably pretty tricky. I have a data management issue that I have been able to resolve using SUMIFs and a custom table, but not in pivot form. Since the data source is a SQL database, I would prefer to resolve the issue in pivot form so that future values chart correctly. The data is formatted as so:

Job	Day Start	Day End	Size	Project
1	1/1/2014	1/10/2014	10	A
2	1/3/2014	3/12/2015	13	B
3	1/6/2014	2/10/2014	50	C
4	1/18/2014	2/10/2014	40	A
5	1/7/2014	3/1/2014	35	B
6	2/16/2014	4/2/2014	23	C
7	1/29/2014	2/28/2014	70	D
8	3/1/2014	3/18/2014	2	E
9	1/4/2014	2/7/2014	12	G
10	2/9/2014	3/9/2014	300	F

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

From that data I want to see the total size of projects active at the start of the week for the past year. This would essentially mean summing the size of the projects if the week start date lies between the day start and end. This is what I ended up with:

Year	Month	Week	Weekday Start	Active Size
2014	1	1	1/1/14	10
2014	1	2	1/8/14	120
2014	1	3	1/15/14	110
2014	1	4	1/22/14	150
2014	1	5	1/29/14	220
2014	2	6	2/5/14	220
2014	2	7	2/12/14	418
2014	2	8	2/19/14	441
2014	2	9	2/26/14	441
2014	3	10	3/5/14	338
2014	3	11	3/12/14	38
2014	3	12	3/19/14	36
2014	3	13	3/26/14	36
2014	4	14	4/2/14	36
2014	4	15	4/9/14	13
2014	4	16	4/16/14	13
2014	4	17	4/23/14	13
2014	4	18	4/30/14	13

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

This is the associated pivot chart:



This would be "OK" except that I would also like to see the size breakdown by project, like this:



This is where I would need the data to be handled purely in a pivot so that when a new project is added, the pivot table will automatically make the summing column for it rather than my manual creation of columns for the data.

I have uploaded a link to a work book with the examples of the data and how I made the charts above. You can see that to make the second chart I had to manually create the columns for each project:

Download PivotExample.xlsx from Sendspace.com - send big files the easy way

Thanks for the help.

 

[Fazza]

hi & welcome

I didn't download the sample file, btw.

I started with the posted first table of data, and named it "ProjectData". Then the first four columns of the second table: named it "WeeksData". Saved the file.

From a new file, ALT-D-P to start the pivot table wizard, external data choice at the first step & followed the wizard. At the end took the option to edit in MS Query and changed the SQL to be as below. Proceed to end.

hth. regards

SELECT W.[Year], W.[Month], W.[Week], P.Size, P.Project
FROM ProjectData P, WeeksData W
WHERE P.[Day Start] <= W.[Weekday Start] AND P.[Day End] >= W.[Weekday Start]

 

[rudypoochris]

hi & welcome

I didn't download the sample file, btw.

I started with the posted first table of data, and named it "ProjectData". Then the first four columns of the second table: named it "WeeksData". Saved the file.

From a new file, ALT-D-P to start the pivot table wizard, external data choice at the first step & followed the wizard. At the end took the option to edit in MS Query and changed the SQL to be as below. Proceed to end.

hth. regards

SELECT W.[Year], W.[Month], W.[Week], P.Size, P.Project
FROM ProjectData P, WeeksData W
WHERE P.[Day Start] <= W.[Weekday Start] AND P.[Day End] >= W.[Weekday Start]

Hmm. That didn't work for me. I wasn't able to bring both files into the pivot table. Additionally the data set will be coming from a SQL connection so saving the data into an Excel file will not be a viable method of doing this. I can though link directly to the SQL database using the query I made to obtain the data in the first place. Unfortunately in the ALT D-P menu it doesn't seem that that is an option.

Do you have any alternate ways that would work with active SQL connections? I could also modify the query itself theorehtically (I know you can do some pivoting in SQL), but I am not sure how.

The ProjectData portion is from a SQL server, the WeeksData was just a blank table I built in Excel.

 

[Fazza]

OK. I had the data in one Excel file not two. If you were to use two files, include the full file path in addition to the table name. Something like `C:\path\filename.xlsx`.ProjectData

Another thought, if you have the data on two separate worksheets, instead of the defined name approach I originally described there might be benefits of using the alternative approach of referring to the sheet names (if the headers are in row 1 and the data immediately below). Syntax is then FROM `full file path`.[sheet name$]

However if the data is coming from a database, just connect straight to it: exactly how that is done for your database I can't advise. Suggest you google.

I'm only familiar with MS Access. If it proves difficult to connect the pivot table to the database suggest you use VBA and ADO and try http://www.connectionstrings.com/ for the connection string. If you have a recordset, then you can set the pivotcache's recordset to that same recordset and quickly create a pivot table: VBA help on recordsets refers, IIRC.

For the WeeksData table there are some choices. Maybe easiest to make/have that table in the database. Also the SQL I gave could be there too. Then the pivot table can simply refresh from that query, or be created on the fly. This would keep the info in the database and the Excel file would be only the pivot table. If having the table in the database doesn't suit, either make it (in Excel most likely) via VBA or, without VBA, have it in a file (probably the same Excel file as the pivot table would be simplest).

regards