Visualizing Cumulative YTD and MTD Financial Data

Published on November 30, 2015

Year-to-date (YTD) and month-to-date (MTD) are common financial terms in financial and operational reporting; people use YTD and MTD information to identify trends or measuring performance against goals.

In this post, I’ll show you get the YTD sales information of each sales rep in an organization.

Dataset
I’ll be working with a sample sales dataset [data source] showing sales information of an organization.

Prerequisites
Access the above-mentioned dataset. If you do not have this dataset, you can install it in Arcadia Instant by following these steps:
Creating data connections
Importing data from flat files
Creating datasets from imported data

The dataset looks like the following table:

rtaimage

The following steps demonstrate how to calculate and show YTD information in a visual.

  1. Create a Cross Tabulation Chart with the following fields on the X and Measures shelves.

rtaimage01

  1. To show the YTD Sales information, write the following expression in the Enter/Edit Expression window of the orderdate variable.
Case when strftime(‘%Y’, [orderdate])==strftime(‘%Y’, ‘now’) then [total] else 0 end as ‘YTD sale’

rtaimage02

  1. Click Refresh Visual.

The following chart appears on the main screen. It shows the YTD sales information of each sales rep.

rtaimage03

  1. Similarly, you can see the MTD sales information of each sales rep by using the following formula in the Enter/Edit Expression Window of the orderdate variable.
Case when strftime(‘%m’, [orderdate])==strftime(‘%m’, ‘now’) then [total] else 0 end as ‘MTD sale’

If you have the revenue data, you can get the YTD or MTD revenue information following the same steps.