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)?

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.  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. input: columns ([Major Id], [Minor Desc], [Year], [Amount]); limit by: active filtering scheme
   2. output: Data table: create 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 rows: Do 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 with: Specific 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 Enhancement request for subtotals for rows on Ideas Portal:

• https://ideas.tibco.com/ideas/TS-I-6880