Pivot Table Formula

[daveWP]

Hi guys, I have a table of data in excel, that I'm merging into a pivot table.

One of the fields is called "Transaction Type", which is coded in each cell as either "Debit" or "Credit", with no null values.

One of the fields I want to calculate in my report is the (debit - credit). I know how to do cross field calculations, but I was wondering if its possible to do this type of calculation within a field based on this criteria without changing the source data?

For instance, a work around would be putting a negative (-) infront of all the values in the rows coded as "credit" and just using the regular subtotal feature.

But that would be changing the data. I'm also trying to accomplish this without using a crutch column.

I'm working with excel 2003.

 

[taigovinda]

I have the exact same situation on a file that I maintain. The raw data comes in as all positive numbers, but I need the credits to show as negative in my pivot table.

And I use the work-around that you mentioned - multiply all the credits by negative one. If there is a way to make your pivot table do calculations within a field, I don't know what it is.

One thing that you could do, though, is to create a macro that sets the amounts to be negative for all the credits in your raw data (regardless of whether they are positive or negative before the macro runs). Then you could set that code to run on the PivotTableUpdate event. If you ever need to see your raw data in its original form, you can just set the amount column to its absolute value (with another macro if desired).

 

[Fazza]

Hi,

I'm going to assume the source data - headers & data - are given the non-dynamic defined name "MyData". From a separate workbook, at the first step of the pivot table wizard take the external data option and follow the wizard. At the end, take the option to edit in MS Query. Now hit the SQL button and edit the SQL (text) so suit what you want. 'OK' to enter it, see the results dataset, hit the 'open door' icon and complete the pivot table. If you want, the resultant worksheet can be moved into the original source (data) file. The SQL will be like below. Within the SQL, it is not case sensitive, the fields being selected can be in any order, the "AS [whatever]" gives a name to the calculate field: it can be the same name as your current field name for values.

HTH, Fazza

'SQL before you edit it
SELECT yourfieldname1, yourfieldname2, etc
FROM MyData
 
'SQL after editing
SELECT IIF(`Transaction Type` = 'Debit', 1, -1)*YourValueField AS [whatever], yourfieldname1, yourfieldname2, etc
FROM MyData

 

[daveWP]

What an interesting solution, Thanks.

 

[Fazza]

Lots more 'work' can be done to the data using the SQL. Routine are joining tables (from different workbooks or the same), aggregation, filtering, etc.

 

[KenV]

Hi,

I'm going to assume the source data - headers & data - are given the non-dynamic defined name "MyData". From a separate workbook, at the first step of the pivot table wizard take the external data option and follow the wizard. At the end, take the option to edit in MS Query. Now hit the SQL button and edit the SQL (text) so suit what you want. 'OK' to enter it, see the results dataset, hit the 'open door' icon and complete the pivot table. If you want, the resultant worksheet can be moved into the original source (data) file. The SQL will be like below. Within the SQL, it is not case sensitive, the fields being selected can be in any order, the "AS [whatever]" gives a name to the calculate field: it can be the same name as your current field name for values.

HTH, Fazza

'SQL before you edit it
SELECT yourfieldname1, yourfieldname2, etc
FROM MyData
 
'SQL after editing
SELECT IIF(`Transaction Type` = 'Debit', 1, -1)*YourValueField AS [whatever], yourfieldname1, yourfieldname2, etc
FROM MyData

Hey Fazza, Here is my Sql Statement. Tried to insert the Select statement at the bottom. It bombed on me. Kind of new SQL. What am I missing?

SELECT CarrAcctTrans.Aged_Bill_Date, CarrAcctTrans.Amount, CarrAcctTrans.Batch_Id, CarrAcctTrans.Batch_Line, CarrAcctTrans.Carrier_Code, CarrAcctTrans.charge_Credit_Code, CarrAcctTrans.Date_Entered, CarrAcctTrans.Effective_Date, CarrAcctTrans.Operator_Id, CarrAcctTrans.Posted_Date, CarrAcctTrans.Publication_Code, CarrAcctTrans.Remarks, CarrAcctTrans.Route_Code, CarrAcctTrans.Time_Entered, Carrier.Name, Carrier.Billing_Period
FROM CirculationReports.dbo.CarrAcctTrans CarrAcctTrans, CirculationReports.dbo.Carrier Carrier
WHERE Carrier.Carrier_Code = CarrAcctTrans.Carrier_Code AND ((CarrAcctTrans.charge_Credit_Code Like '%AINS%') OR (CarrAcctTrans.charge_Credit_Code Like '%AINA%') OR (CarrAcctTrans.charge_Credit_Code Like '%HINS%') OR (CarrAcctTrans.charge_Credit_Code Like '%HINA%') OR (CarrAcctTrans.charge_Credit_Code Like '%OINS%') OR (CarrAcctTrans.charge_Credit_Code Like '%OINA%'))

SELECT IIF(CarrAcctTrans.Carrier.Carrier_Code = 'AINA',CarrAcctTrans.Amount*-1,CarrAcctTrans.Amount*1)

 

[Fazza]

Just about there, Ken. Syntax for the SQL is like I posted earlier. To add another field, it has to be in the 'SELECT' clause. So, add to the list after SELECT and before FROM - again, as posted earlier. That is about all you have to change. Use a comma to separate from other selected fields. The AmountNewName can be the existing name Amount, btw.

Also, table aliases can be used. In the 'FROM' clause the table name is shown with the alias after. Use the alias elsewhere (other than the FROM) for conciseness.

So, for your example,

SELECT T.Aged_Bill_Date, IIF(C.Carrier_Code = 'AINA',-1,1)*T.Amount AS [AmountNewName], T.Batch_Id, T.Batch_Line, T.Carrier_Code, T.charge_Credit_Code, T.Date_Entered, T.Effective_Date, T.Operator_Id, T.Posted_Date, T.Publication_Code, T.Remarks, T.Route_Code, T.Time_Entered, C.Name, C.Billing_Period
FROM CarrAcctTrans T, Carrier C
WHERE C.Carrier_Code = T.Carrier_Code AND (T.charge_Credit_Code Like '%AINS%' OR T.charge_Credit_Code Like '%AINA%' OR T.charge_Credit_Code Like '%HINS%' OR T.charge_Credit_Code Like '%HINA%' OR T.charge_Credit_Code Like '%OINS%' OR T.charge_Credit_Code Like '%OINA%')

There is another option in the where clause. You could use it if you want. Instead of

WHERE A.field like 'this' OR A.field like 'that' OR A.field like 'even this'
 
'it can be
WHERE A.field IN ('this', 'that', 'even this')

So for your example, that might be

SELECT T.Aged_Bill_Date, IIF(C.Carrier_Code = 'AINA',-1,1)*T.Amount AS [AmountNewName], T.Batch_Id, T.Batch_Line, T.Carrier_Code, T.charge_Credit_Code, T.Date_Entered, T.Effective_Date, T.Operator_Id, T.Posted_Date, T.Publication_Code, T.Remarks, T.Route_Code, T.Time_Entered, C.Name, C.Billing_Period
FROM CarrAcctTrans T, Carrier C
WHERE C.Carrier_Code = T.Carrier_Code AND T.charge_Credit_Code IN ('%AINS%', '%AINA%', '%HINS%', '%HINA%', '%OINS%', '%OINA%')

An important caveat. Be aware that because "WHERE C.Carrier_Code = T.Carrier_Code" only records that have matching carrier codes are returned. Different approaches are required if unmatched records are to be returned.

HTH, Fazza

 

[taigovinda]

Hi Fazza,

I've never really queried external data like this, but I have a pivot table that someone else set up to query some external data ...

When going into the SQL button, I see part of it that says this:

AND (US_CATSProd.Month=3)

The pivot is refreshing just fine to show what is wanted. Is there a way that I can make that 3 read from a cell value on my sheet, or a named range, or something within the book that contains the pivot table? I would like to not have to go in and edit the query each month, but instead change cell value.

As you can see, I'm pretty lost - but you have to start somewhere! Thanks in advance.

Tai

 

[Fazza]

Hi, Tai.

Maybe a change event on the input cell? I've assumed cell A1 (on the worksheet containing the pivot table) & also that it has data validation (so that only valid/correct entries are possible). Place code in the worksheet code module. OK for Excel 2003 & untested in other versions.

HTH, Fazza

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address Like "$A$1" Then
    Application.EnableEvents = False
      Me.PivotTables(1).PivotCache.Sql = "your revised SQL"
    Application.EnableEvents = True
  End If
End Sub

 

[taigovinda]

Fazza,

You make it look so easy! Thanks for your help so far. I am trying to replace on several pivot tables and here is the code I tried:

Sub Change_SQL_Code()
Dim strMonthRawOld As String, strMonthRawNew As String, strMonthOld As String, strMonthNew As String
Dim strYearRawOld As String, strYearRawNew As String, strYearOld As String, strYearNew As String
Dim strSQL As String, pt As PivotTable, i As Long
strMonthRawOld = [u5]: strMonthRawNew = [v5]
strYearRawOld = [u6]: strYearRawNew = [v6]
For Each pt In ActiveSheet.PivotTables
    '1st variation: "Month=0" and "Year=0"
    strMonthOld = "Month=" & strMonthRawOld
    strMonthNew = "Month=" & strMonthRawNew
    strYearOld = "Year=" & strYearRawOld
    strYearNew = "Year" & strYearRawNew
MsgBox pt.PivotCache.Sql
MsgBox Replace(pt.PivotCache.Sql, strMonthOld, strMonthNew)
pt.PivotCache.Sql = pt.PivotCache.Sql 'run time error 1004
pt.PivotCache.Sql = Replace(pt.PivotCache.Sql, strMonthOld, strMonthNew) 'run time error 1004
    pt.PivotCache.Sql = Replace(pt.PivotCache.Sql, strYearOld, strYearNew)
    '2nd variation: "Month='00'" and "Year='00'"
    strMonthOld = "Month='" & Format(CLng(strMonthRawOld), "00") & "'"
    strMonthNew = "Month='" & Format(CLng(strMonthRawNew), "00") & "'"
    strYearOld = "Year='" & Format(CLng(strYearRawOld), "00") & "'"
    strYearNew = "Year'" & Format(CLng(strYearRawNew), "00") & "'"
    pt.PivotCache.Sql = Replace(pt.PivotCache.Sql, strMonthOld, strMonthNew)
    pt.PivotCache.Sql = Replace(pt.PivotCache.Sql, strYearOld, strYearNew)
Next
End Sub

I can't seem to assign the SQL, though... I get a run-time error 1004 on the red line. So I added in the blue lines to help see what is happening and I get the same error on the third blue line.

I would have expected setting the SQL equal to itself would work! Any idea how to correct this? FYI, I am using Excel 2000. Not sure if that matters, I will be able to try in on 2003 later tonight. Here is the first pivot table's SQL as it comes through if i print it to a cell:

SELECT US_Unprod.Pin, US_Unprod.cent, US_Unprod.tsnumber, US_Unprod.centre, US_Unprod.pct, US_Unprod.mccode, US_Unprod.gross, US_Unprod.dist, US_Unprod.sdist, US_Unprod.jobtype, US_Unprod.busarea, US_Unprod.date, US_Unprod."Dist-Sub", US_Unprod."mccode 6", US_Unprod."mccode 4", US_Unprod."mccode 2", US_Unprod."mccode desc", US_Unprod.Month, US_Unprod.Year, US_Unprod."Quick Sort"
FROM sqlp_cats.oconno05.US_Unprod US_Unprod
WHERE (US_Unprod.Month='03') AND (US_Unprod.Year='10')
ORDER BY US_Unprod.date, US_Unprod.tsnumber

There are some characters that look like this "|" (char(124)) that don't seem to show up when I paste in the code, but they are there before every line break when I look at the cell. Thanks again for all your help.

Tai