Pivot Table Woes

[jonbrown]

Hi All-
I have learned a great deal about pivot tables by reading the articles in this board but this one has me stumped. I have a table with data that is pulled from an odbc database. I then summarize the data into a pivot table.

Rows Across

Currently I have

<TABLE style="WIDTH: 450pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=598 border=0><COLGROUP><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" span=2 width=78><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #b8cce4 1pt solid; WIDTH: 84pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=112 height=20>JUNE UNITS</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: #ece9d8; WIDTH: 26pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=35></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 83pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=110>JUNE SALES</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=71></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 54pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=72>UNITS YTD</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 32pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=42></TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 59pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=78>SALES YTD</TD><TD class=xl63 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #b8cce4 1pt solid; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 59pt; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=78></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20>2007</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2008</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2007</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2008</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2007</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2008</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2007</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; FONT-WEIGHT: 700; FONT-SIZE: 11pt; BACKGROUND: #4f81bd; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: #ece9d8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none">2008</TD></TR></TBODY></TABLE>

What I need however is

June Units 2007 2008 +/-


the plus minus column would be governed by this formula
assuming that June Units 2007 = A1 and June Units 2008 = A2 for sake of argument and example.

=(A1-A2)/A2

To calculate. However I cant seem to be able to generate a calculation like this in excel. Any ideas? Here is the excel file for those who can help!

http://www.donfoshays.com/excel.zip

 

[Andrew Poulsom]

Right click the June Units box and choose Formulas|Calculated Item. Build your formula using the Insert Item button.

 

[mack5511]

If you are using MS Query you can build the calculated fields in the SQL statement that is bringing in your data. Then it will come in as another field in the pivot table wizard. I would say if that field is only needed for the one pivot table to use Andrews method. If you will need it in data across many pivot tables the try my solution.

Mack

 

[jonbrown]

I tried that "Calculated Field" but all my formulas that I put in came back with errors. Here is what I had tried.

=munits__6 'rs-year=2007'- msales__6 'rs-year=2008'/ msales__6 'rs-year=2008'

but I keep getting errors...

What is the correct formula?

 

[mack5511]

Jon,


Give me a little bit of time and I will work out a solution for you.


Mack

 

[jonbrown]

Thanks Mack, I appreciate it!

 

[mack5511]

Ok,

I need some further information from you.


1. What is your knowledge of SQL queries?
2. Have you used MS Query either with the query wizard or just writing a query against an ODBC database?
3. I need the field names from the odbc db to help write the correct SQL query to bring back the calculated fields.
4. Is all the data you need to return in a single table in the database?

Let me know,

Mack

 

[mack5511]

Jon,


Pending a response to my question I will start with a generic SQL statement looks like for MS query.


Select ODBC Field 1, ODBC Field 2, ODBC Field 3,
(ODBC Field 1 - ODBC FIELD 2)/ODBC FIELD 3 as Sales Ratio
FROM ODBC DB Name
WHERE ODBC Field 1 = Year


This would bring 3 fields and 1 calculated field back to Excel with a criteria of the year specified in the where clause.


Mack

 

[jonbrown]

If you have downloaded the attached file you will see that the workbook has two pages, one is the pivot table page, the other is the raw data page which has a link to odbc. Now to get this data I quieried the table using the query wizard. Im pretty good with quieries, however the reason I used a pivot table is that when I dump the data from odbc into excel I get multiple entries for customers with different data sets. The data however needs to be merged together so to speak so I use the pivot table to return a list of customers and sales information based on all the information in the query.

So, in a sence a query may or may not help me because the data for each month is located in a different table, thus resulting in a multiple customer records being imported. The data I need to manipulate is the data that is being summarized by the pivot table.

here is my query

SELECT customer.CNum, customer.L-Name, customer.r-slm1, customer.LocNum, r-sales.munits__1, r-sales.munits__2, r-sales.munits__3, r-sales.munits__4, r-sales.munits__5, r-sales.munits__6, r-sales.munits__7, r-sales.munits__8, r-sales.munits__9, r-sales.munits__10, r-sales.munits__11, r-sales.munits__12, r-sales.msales__1, r-sales.msales__2, r-sales.msales__3, r-sales.msales__4, r-sales.msales__5, r-sales.msales__6, r-sales.msales__7, r-sales.msales__8, r-sales.msales__9, r-sales.msales__10, r-sales.msales__11, r-sales.msales__12, r-sales.rs-year
FROM adist.customer customer, adist.r-sales r-sales
WHERE customer.CNum = r-sales.custnum AND ((customer.LocNum='05'))

the data comes from two tables, r-sales and customer. I hope this helps.

Jon

 

[mack5511]

Jon,

OK glad to hear you have a grasp on MS query it seems like alot of people overlook it as a way to process ODBC data.

This should give you a calculated field
based on the formula entered earlier.
Depending on the version of Excel and MS Query you have you may be able to use aliaes for your field names.


SELECT customer.CNum,
customer.L-Name ,
customer.r-slm1 ,
customer.LocNum ,
r-sales.munits__1 ,
r-sales.munits__2 ,
r-sales.munits__3 ,
r-sales.munits__4 ,
r-sales.munits__5 ,
r-sales.munits__6 ,
(
(SELECT r-sales.munits__6 FROM r-sales WHERE r-sales.rs-YEAR = 2007
)-
(SELECT r-sales.msales__6 FROM r-sales WHERE r-sales.rs-YEAR = 2008
) /
(SELECT r-sales.msales__6 FROM r-sales WHERE r-sales.rs-YEAR = 2008
)) as Ratios ,
r -sales.munits__7 ,
r -sales.munits__8 ,
r -sales.munits__9 ,
r -sales.munits__10 ,
r -sales.munits__11 ,
r -sales.munits__12 ,
r -sales.msales__1 ,
r -sales.msales__2 ,
r -sales.msales__3 ,
r -sales.msales__4 ,
r -sales.msales__5 ,
r -sales.msales__6 ,
r -sales.msales__7 ,
r -sales.msales__8 ,
r -sales.msales__9 ,
r -sales.msales__10 ,
r -sales.msales__11 ,
r -sales.msales__12 ,
r -sales.rs-YEAR
FROM adist.customer customer,
adist.r -sales r-sales
WHERE customer.CNum = r-sales.custnum
AND ((customer.LocNum ='05'))


Let me know if this works.


Mack