Accounts Payable Data Model

Modified on Wed, 29 Jul at 1:40 AM

Model Summary

The QQube Accounts Payable Data Model analyzes open payables, applied amounts, aging, payment behavior, and vendor activity as of the date selected in the QQube Configuration Tool.

Model Type	Accounts payable and aging analysis
Transaction Coverage	Linked posting and non-posting transactions
Aging Basis	Transaction Date and Due Date
Available Fields	More than 850
Primary Use	Analyze balances, aging, payment timing, discounts, and vendor activity

Best Used For

Aging payables by Transaction Date or Due Date.
Creating traditional 30-60-90 aging reports.
Preparing pre-check reports.
Reviewing vendor notes and available discounts.
Comparing average days past due with average payment terms.
Reviewing payables by job dates, job status, active jobs, or jobs with active estimates.

Key Capabilities

Includes linked posting and non-posting transactions.
Provides Transaction Date and Due Date aging in the same model.
Includes original, applied, and open payable amounts.
Includes printed and base units of measure.
Includes home and foreign currency values.

QuickBooks Data Limitations

Accounts Payable aging is not dynamic.

Enter the required As Of Date in the QQube Configuration Tool and run a synchronization to retrieve the appropriate aging data.

Quantity, Rate, Amount, and Aging Fields

Native fields come from QuickBooks Accounts Payable data. QQube field extensions add printed-unit values for analysis.

Native Fields (20)

Quantity and Rate Fields	Amount Fields	Aging Fields
Line Quantity Line Rate Line Rate Percent Line Exchange Rate	Line Original Amount Line Original Amount (Foreign) Line Applied To A/P Amount Line Applied To A/P Amount (Foreign) Line Open A/P Amount Line Open A/P Amount (Foreign)	Txn Date Aging Current Txn Date Aging Bucket1 Txn Date Aging Bucket2 Txn Date Aging Bucket3 Txn Date Aging Bucket4 Due Date Aging Current Due Date Aging Bucket1 Due Date Aging Bucket2 Due Date Aging Bucket3 Due Date Aging Bucket4

QQube Field Extensions (2)

Quantity Fields	Rate Fields	Amount Fields
Line Quantity [Printed]	Line Rate [Printed]

Power BI and Excel Power Pivot Measures

The prepared Accounts Payable examples include 33 visible DAX measures. Hidden measures are not included.

Measure Section	Visible Measures
Power BI Company-Filter Measures	5
.Base Measures	22
Average Days Measures	2
Calendar Pattern Measures	4

Power BI company filtering

The Account, Class, Job, Split Account Offset, and Vendor slicers use bridge tables and five support measures to show only values associated with the selected company. Excel Power Pivot handles this filtering automatically.

Included Dimensions

Calendars

Due Date	Suggested Discount Date	Transaction Date
Job End Date	Job Projected End Date	Job Start Date
Calendar Overview

Document

Document Attributes

Line Attributes

Geography

Lists

Account (Purchases)	Account - Expense Offset	Class
Company	Currency	Customer
Customer Rep	Employee	Item
Job	Job Rep	Other Name
Sales Rep	Terms	Vendor

Linked Information

Payables Linked Transactions

Account (Cash Source)

Technical Details

Model Structure	One Accounts Payable FACT table with related dimensions and relationships already included
As Of Date	Selected in the QQube Configuration Tool before synchronization
Aging Behavior	Fixed to the synchronized As Of Date; not dynamically recalculated
DAX Availability	Power BI and Excel Power Pivot examples

Expected Result

Enter the required As Of Date, run a synchronization, and open the prepared example.

The FACT table, dimensions, relationships, fields, QQube extensions, and measures are already included.