Skip to main content

Lesson 10: Aging Buckets – Credit Card Transactions

In this lesson, we want to include credit card transactions that are not part of expense reports, so we need to use the Transactions report object. Before we put the data into presentable form, we will look at the underlying information. This will help ensure that our calculations are correct.

Create a New Report

  • Right-click on the Transactions Data Object and select NEW > REPORT.
  • Add the following column to the report as dimensions (BY):
  • Transaction (Segment) - Owner (Segment) – LASTNAME (Field)
  • Transaction (Segment) - EXPENSETRANSACTIONID (Field)
  • Transaction (Segment) - TRANSACTIONDATE (Field)
  • Transaction (Segment) - STATEMENTDATE (Field)
  • Transaction (Segment) – LineItem (Segment) - STATUSAPPROVED (Field)
  • Add the following column to the report as measure (Sum):
  • Transaction (Segment) - AMOUNTSPENT (Field)

Create a Virtual Field for Days Past Due

  1. In the Data tab, in the Calculation group, select Detail (Define).
  2. Name the field DaysPastDue.
  3. The definition should read as follows: DATEDIF (TRANSLINEITEMHEADERD.Transaction.STATEMENTDATE , TRANSLINEITEMHEADERD.Transaction.Today , 'D' )

  4. Add the new DaysPastDue field to the report as a SUM.

Create Virtual Fields for Each Aging Bucket

We want to add three fields to the report:

  1. Transactions that are not yet due (today is less than the statement date).
  2. Transactions that are one month overdue (less than or equal to 30 days past due)
  3. Transactions that are more than 30 days past due.

In each field, we want to display the AmountSpent for transactions in that category.

  1. Create a field called NotDue: IF DaysPastDue LE 0 THEN TRANSLINEITEMHEADERD.Transaction.AMOUNTSPENT ELSE 0
  2. Create a field called PastDue30: IF DaysPastDue GT 0 AND DaysPastDue LE 30 THEN TRANSLINEITEMHEADERD.Transaction.AMOUNTSPENT ELSE 0
  3. Create a field called Over30: IF DaysPastDue GT 30 THEN TRANSLINEITEMHEADERD.Transaction.AMOUNTSPENT ELSE 0

Add All of the Virtual Columns to the Report as Measures

The new fields should appear at the bottom of the Transaction segment.

Add all three field as Measures (Sum) to the report. Don't worry about the strange numbers for DaysPastDue on a few rows. Check the rest for reasonableness.

Filter Out Non-Credit Card Transactions

The Transaction segment contains more than just credit card expenses, so we will need a filter.

  1. Add Transaction (Segment) - TBL_FEED (Segment) - NAME (field) as a dimension. This will show where each transaction comes from.
  2. Filter the field to only show Credit Card.

Create a Virtual Field That Shows Whether a Transaction Has Been Used

We will use the STATUSAPPROVED on an expense entry to determine if an entry has been used. In this report, we only want to include items that have a status of DRAFT and entries that are not linked to any expense entry.

  1. In the Data tab, in the Calculation group, select Detail (Define).
  2. Name the field ItemStatus.
  3. Click FORMAT and select Alphanumeric from the Field Type list of formats.
  4. Enter the following IF condition and click OK:

IF TRANSLINEITEMHEADERD.LineItem.STATUSAPPROVED EQ 'PEND' OR TRANSLINEITEMHEADERD.LineItem.STATUSAPPROVED EQ 'APP' OR TRANSLINEITEMHEADERD.LineItem.STATUSAPPROVED EQ 'RET' OR TRANSLINEITEMHEADERD.LineItem.STATUSAPPROVED EQ 'NOAC' OR TRANSLINEITEMHEADERD.LineItem.STATUSAPPROVED EQ 'NAM' THEN 'USED' ELSE 'UNUSED'

Add a Filter to Select Only Unused Items

  1. Use the search box at the top of the Data panel to find the new ItemStatus field you created, then drag it down to SUM to add it as a measure.

  2. Right click on the ItemStatus field and select Filter Values.
  3. Add a filter so that ItemStatus equals UNUSED.

Remove Unnecessary Columns

Now we can delete all the unneeded columns:

  1. Hide the column ItemStatus.
  2. Delete all the dimensions except for LASTNAME.
  3. Delete the DaysPastDue field.
  4. Change the column titles as shown below.

Save and Run the Report

  1. In the Quick Access Toolbar, click the Save button. Save the report in this location: Content > [Your Company Name] > My Content.
  2. Name the report “Lesson 10 - Aging Buckets - Credit Card Transactions."
  3. In the Quick Access Toolbar, click the Run button.

Was this article helpful?