How to add dynamic year-to-date (YTD) calculation in a cross table (as subtotals for rows)?

How to add dynamic year-to-date (YTD) calculation in a cross table (as subtotals for rows)?

book

Article ID: KB0075200

calendar_today

Updated On:

Products Versions
Spotfire Analyst All

Description

If you have some monthly data in a cross table with the dates on the horizontal axis (e.g. Year > Month), and you want to see a year-to-date (YTD) column for each year in the cross table, you are essentially looking for a subtotals for rows. This article provides an option using data transformation and a simple data function to achieve this requirement. 

Resolution

See the attached dxp file "Cross table subtotals for rows.dxp" for reference.
  1. Use data transformations to create two new columns [Year] and [Month] in your original data table.
    1. Go to Data Transform Data < select your original data table < select Calculate new column for Transformations Insert.
      • Expression: Year([Date]); column name: Year  < OK.
    2. Insert another Calculate new column for Transformations after the first one.
      • Expression: Month([Date]); column name: Month  < OK.
    3. Insert Change data types transformation as the third transformation.
      • Change the data type of column [Month] from Integer to String. This is to add "YTD" to the column in the future.
    4. OK.
  2. Go to Data Data Function Properties < Register New to register a new data function.
    1. Define the data function: script: output = input
    2. input parameter: input, type: table, allowed data types: all
    3. output parameter: output, type: table
    4. Run the script.
  3. Define the parameters:
    1. inputcolumns ([Major Id], [Minor Desc], [Year], [Amount]); limit by: active filtering scheme
    2. outputData tablecreate new data table: YTD.
    3. Check the box for "Refresh function automatically".
  4. Pivot the YTD table.
    1. Data Transform Data < select YTD, and select Pivot for Transformations.
    2. Row identifiers: [Major Id], [Minor Desc], [Year]
    3. Column titles: None
    4. Values and aggregation methods: Sum(Amount); column naming pattern: %V
    5. OK OK.
  5. Add rows from your original data table to the new YTD table.
    1. Match all 4 columns.
    2. Include additional columns from new data: choose column [Month].
    3. Identify origin of rowsDo not identify origin.
    4. OK.
  6. In the Data in analysis panel, locate the [Month] column in the YTD table. Click on the gear icon to see Details on selected column.
    1. Find Empty values.
    2. Replace empty values withSpecific value: YTD.
  7. Go to Data Column Properties < find the [Month] column in the YTD table < Sort Order Custom sort order < Configure.
    1. Order the values from 1 to 12, and YTD as the last one.
    2. OK.
  8. Create a cross table from table YTD.
    1. Horizontal Axis: add columns [Year] and [Month].
    2. Vertical Axis: add columns [Major Id] and [Minor Desc]
    3. Value Axis: Sum(Amount)
There is an existing enhancement request to make this feature available out of the box. Please vote for this idea at https://ideas.tibco.com/ideas/TS-I-6880

Issue/Introduction

This article explains how to add dynamic year-to-date (YTD) calculation in a cross table (as subtotals for rows).

Additional Information

Enhancement request for subtotals for rows on Ideas Portal:

Attachments

How to add dynamic year-to-date (YTD) calculation in a cross table (as subtotals for rows)? get_app