5. Add a PYtD vs CYtD percentage calculation

This topic shows you how to create a calculation to determine the difference between the Current Year-to-Date Sales Order Amounts and the Previous Year-to-Date Sales Order Amounts. The calculation then divides the difference by the previous year amount to get a percent for the Sales Order amount.

Subscription Interactive Visual Explorer
Regional availability

All regions
User type

Business, Project Manager, and Warehouse
Permissions
  • Platform Services: Run, List, View, Add, Edit, Delete for Interactive Visual Explorer
  • Permissions for the data the visual board contains

Before you begin

This topic builds on the tasks in Create a project for sales order performance and the previous procedures in the workflow. If you have not already done so, complete these tasks before continuing with this topic.

Step 1: Create a the percentage calculation

This calculation is based on the previous calculations you created:

The simplest way to create this calculation is to leverage the calculations you created previously. You do this by copying and combing them, then adding the appropriate operators to create the percentage calculation.

The completed calculation looks like the following example. The newly added operators, minus (-) and divide by (\) are shown on separate lines between the combined calculation segments.

Copy 
((FILTER(TOTAL USING(YEAR(WHENCREATED) = YEAR(TIMESTAMPADD(SQL_TSI_YEAR, 0,
CURRENT_DATE))and WHENCREATED < TIMESTAMPADD(SQL_TSI_YEAR, 0, CURRENT_DATE))))
- 
(FILTER(TOTAL USING(YEAR(WHENCREATED) = YEAR(TIMESTAMPADD(SQL_TSI_YEAR, -1,
CURRENT_DATE))and WHENCREATED < TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE)))))
/ 
(FILTER(TOTAL USING(YEAR(WHENCREATED) = YEAR(TIMESTAMPADD(SQL_TSI_YEAR, -1,
CURRENT_DATE))and WHENCREATED < TIMESTAMPADD(SQL_TSI_YEAR, -1, CURRENT_DATE))))

Step 2: Combine CYtD and PYtD calculations to create a percentage

  1. Open the Sales Order Overview visual board.
  2. Go to My Calculation > CYtD SO Amount > Edit Calculation, copy the calculation to the clipboard, then select Cancel to close the dialog.
  3. Select My Calculations > Add Calculation, enter CYtD vs PYtD % SO Amount for the Name, then paste the CYtD SO Amount calculation into the text field and Save the calculation.
  4. Go to My Calculations > PYtD SO Amount > Edit Calculation, copy the calculation to the clipboard, and select Cancel to close the dialog.
  5. Go to My Calculations > CYtD vs PYtD % SO Amount > Edit Calculation and enter a new line after the CYtD SO Amount calculation.
  6. Enter a minus sign (-) on the new line, paste the PYtD SO Amount calculation on a new line, then enter another new line followed by a divide by sign (/) and another new line.
  7. Paste another PYtD SO Amount calculation beneath the divide by sign (/).
  8. Validate and Save the calculation, then Save the visual board.

    The Edit Calculation dialog shows the complete CYtD vs PYtD % SO Amount calculation syntax validated.

  9. Continue with Explore with a Price by Year and Quarter waterfall chart.