Advanced Formula Query Help

Hi,

I'm hoping someone with a bit of technical knowledge into QB order of operations can help me better structure my formula query so it can run more efficiently. I've tried reaching out directly to Quickbase, but sadly have not heard back.

When I first created this, it ran and saved just fine. Now that we have 3 years of data in the app, the more data intensive fields are struggling to run (e.g. Budget & Forecast; which are always a full year worth of data).

Description of my code:

This code is providing a number that will be nicely displayed on a financial report. To my knowledge, this could not be solved another way, unless I wanted to create dozens of relationships and hundreds of fields to cover the dozen reports I will be creating all with their own variations of this formula.

There is an Actuals, Budget, and Forecast table the data will be pulled from for the formulas I construct.
- Budget and Forecast are much more data intensive, with about 57k records each in their respective tables that this formula is looking at.
My code first looks at if the record belongs on the report this is for. If not, it will not display a number (maybe I move this into an if statement at the bottom of my code?).
I then establish a couple variables that will help me properly match it with the data via formula query.
The FlipValues is for a couple rows that need their values inverted for reporting purposes.
There are "summary rows" on the financial reports. Some of them have no data and are there for visual purposes. Others do have data, but their data is based on what is queried in the BudgetSum variable.
The BudgetSum variable is looking at where the row name in the reporting table matches the applicable field in the Budget table. This is what has allowed me to create one field to encompass 40 different values.
Below the BudgetSum variable are the variables for the summary rows. These are a combination of values via addition/subtraction.
At the end, if it is one of the core fields, it will use the BudgetSum variable, and if it is a summary row, it will use the applicable summary row variable.
The final value is fed into a rich text field for final display on the report.

//This formula is fed to the Budget QTD Rich Text field to be displayed on report

var bool IsApplicableRow = //Formula will be blank for N/A rows to save time/speed of formula.
If(Contains([Report Row - Applicable Report], "QTD Budget Variance"), true, false);

var text Period = Left(ToText([Reporting Period - Period]), 4) & "12";

var text ClosingMonth = Right(ToText([Reporting Period - Period]), 2);

var bool FlipValues =
Case([Report Row Name],
"Retail revenues", true,
"Alternative Revenue Programs", true,
"Other revenues", true, false);

var bool IsNotSummaryRow =
If([Report Row Name]="Revenues:"
or [Report Row Name]="Total Revenues"
or [Report Row Name]="Gross Margin"
or [Report Row Name]="Fixed O&M Expenses"
or [Report Row Name]="Total Fixed O&M Expenses"
or [Report Row Name]="Other Expenses"
or [Report Row Name]="Total Other Expenses"
or [Report Row Name]="UTILITY OPERATING INCOME"
or [Report Row Name]="Other (Income) & Deductions"
or [Report Row Name]="Total Other (Income) & Deductions"
or [Report Row Name]="INCOME BEFORE INTEREST AND TAXES"
or [Report Row Name]="Interest Expenses:"
or [Report Row Name]="Total Interest Expenses"
or [Report Row Name]="INCOME BEFORE INCOME TAXES"
or [Report Row Name]="Taxes"
or [Report Row Name]="Total Taxes"
or [Report Row Name]="NET INCOME"
or [Report Row Name]="NET INCOME FOR PGE", false, true);

/////Section Start - the comment on the line below applies to all variables through "/////Section End"
/////This section sums all budget values where the Report Row Name = SEC BS Flux & Mo Acctg Review on the Actuals table and the Reporting Periods are the same
//12 = "Forecast Income"; 8 = "Budget Quarter"; 9 = "Amount"; 43 = "Closing Month"
var number BudgetSum =
SumValues(GetRecords("{12.EX.'"&[Report Row Name]&"'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)*(If($FlipValues=true, -1, 1));

var number TotalRevenuesValue = //Total Revenues
-1*(SumValues(GetRecords("{12.EX.'Retail revenues'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Alternative Revenue Programs'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Other revenues'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9));

var number GrossMarginValue = //Gross Margin
$TotalRevenuesValue
- (SumValues(GetRecords("{12.EX.'Purchased power and fuel'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Gas for resale'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Wholesale revenues'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9));

var number TotalFixedValue = //Total Fixed O&M Expenses
SumValues(GetRecords("{12.EX.'Fixed plant costs'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Transmission'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Distribution'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Customer accounts'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Customer service'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Administrative and general'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Insurance and benefits'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9);

var number TotalOtherExpensesValue = //Total Other Expenses
SumValues(GetRecords("{12.EX.'Depreciation and amortization'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Property taxes'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Payroll taxes'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Other taxes'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9);

var number UtilityOperatingIncomeValue = // UTILITY OPERATING INCOME
$GrossMarginValue
- $TotalFixedValue
- $TotalOtherExpensesValue;

var number TotalOtherIncDedValue = //Total Other (income) & Deductions
SumValues(GetRecords("{12.EX.'AFDC Equity Only'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Miscellaneous income (expense)'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9);

var number IncBefIntTaxValue = //INCOME BEFORE INTEREST AND TAXES
$UtilityOperatingIncomevalue
- $TotalOtherIncDedValue;

var number TotalInterestExpensesValue = //Total Interest Expenses
SumValues(GetRecords("{12.EX.'Other interest & amortization'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'AFDC debt'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Short term interest'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Long term interest'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9);

var number IncBefIncTaxValue = //Income Before Income Taxes
$IncBefIntTaxValue
- $TotalInterestExpensesValue;

var number TotalTaxesValue = //Total Taxes
SumValues(GetRecords("{12.EX.'Utility income taxes'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9)
+ SumValues(GetRecords("{12.EX.'Nonutility income taxes'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9);

var number NetIncomeValue = //NET INCOME
$IncBefIncTaxValue
- $TotalTaxesValue;

var number NoncontrollingIntValue = //Noncontrolling Interest
SumValues(GetRecords("{12.EX.'Noncontrolling Interest'}AND{8.EX.'"&$Period&"'}AND{43.EX.'"&$ClosingMonth&"'}", [_DBID_BUDGETS]), 9);

var number NetIncomePGEValue = //NET INCOME FOR PGE
$NetIncomeValue
- $NoncontrollingIntValue;
/////Section End

var number ValueToFormat = //This will give us a number. The number will then be put through the number formating formula (below this variable) for presentation.
//This IF statement is going to give the budget value if it is not a summary row. If it is a summary row, it will provide the number based on the calculations above. These calculations are based on the WDesk spreadsheets' math.
If($IsNotSummaryRow=true, $BudgetSum,
Case([Report Row Name],
"Total Revenues", $TotalRevenuesValue,
"Gross Margin", $GrossMarginValue,
"Total Fixed O&M Expenses", $TotalFixedValue,
"Total Other Expenses", $TotalOtherExpensesValue,
"UTILITY OPERATING INCOME", $UtilityOperatingIncomeValue,
"Total Other (Income) & Deductions", $TotalOtherIncDedValue,
"INCOME BEFORE INTEREST AND TAXES", $IncBefIntTaxValue,
"Total Interest Expenses", $TotalInterestExpensesValue,
"INCOME BEFORE INCOME TAXES", $IncBefIncTaxValue,
"Total Taxes", $TotalTaxesValue,
"NET INCOME", $NetIncomeValue,
"NET INCOME FOR PGE", $NetIncomePGEValue
));

//This is the final value and will be non-"$ Formula - " version of this field for display purposes.
If($IsApplicableRow=true, $ValueToFormat, null)

------------------------------
Curtis Middleton
------------------------------

Forum Discussion

Advanced Formula Query Help