Nested Table Calculations

🕐 2 min read

VDS supports nested table calculations. If you have one table calculation within another one, you can set the dimensions for compute Using independently for each table calculation referenced. To configure nested calculations independently, use:

tableCalcType": "NESTED"

For example, if you have three calculations saved on your published data source:

1-nest, with the formula TOTAL(SUM([Sales])) (a table calc)
2-nest, with the formula TOTAL(SUM([Profit]))(also a table calc)
3-nest, with the formula [1-nest] + [2-nest] (not a table calc in itself, but has two nested table calculations)

Our goal is to query the calculation 1-nest. In this case, you want to compute the dimensions of 1-nest and 2-nest independently. You could do this with the following query:

 "query": {
   "fields": [
     {
       "fieldCaption": "Region",
       "sortPriority": 1
     },
     {
       "fieldCaption": "Segment",
       "sortPriority": 2
     },
     {
       "fieldCaption": "Order Date",
       "function": "YEAR",
       "sortPriority": 3
     },
     {
       "fieldCaption": "3-nest",
       "tableCalculation": {
         "tableCalcType": "CUSTOM",
         "dimensions": [
         ]
       },
       "nestedTableCalculations": [
         {
           "fieldCaption": "1-nest",
           "tableCalcType": "NESTED",
           "dimensions": [
             {
               "fieldCaption": "Region"
             },
             {
               "fieldCaption": "Segment"
             },
             {
               "fieldCaption": "Order Date",
               "function": "YEAR"
             }
           ]
         },
         {
           "fieldCaption": "2-nest",
           "tableCalcType": "NESTED",
           "dimensions": [
             {
               "fieldCaption": "Region"
             },
             {
               "fieldCaption": "Segment"
             }
           ],
           "restartEvery": {
             "fieldCaption": "Region"
           }
         }
       ]
     }
   ]
 }

Because 1-nest isn’t a table calculation in itself, the dimensions list is empty.

Let’s say you want to query another calculation saved on your published data source, called 4-nest. You intend to use the formula WINDOW_SUM(SUM([SALES]), -2, 0) - [2-nest].

Because 4-nest is a table calculation in itself (WINDOW_SUM), but so is 2-nest,. You can add dimensions for Compute Using for each calculation.

 "query": {
    "fields": [
      {
        "fieldCaption": "Region",
        "sortPriority": 1
      },
      {
        "fieldCaption": "Segment",
        "sortPriority": 2
      },
      {
        "fieldCaption": "Order Date",
        "function": "YEAR",
        "sortPriority": 3
      },
      {
        "fieldCaption": "4-nest",
        "tableCalculation": {
          "tableCalcType": "CUSTOM",
          "dimensions": [
            {
              "fieldCaption": "Region"
            },
            {
              "fieldCaption": "Segment"
            },
            {
              "fieldCaption": "Order Date",
              "function": "YEAR"
            }
          ]
        },
        "nestedTableCalculations": [
          {
            "fieldCaption": "2-nest",
            "tableCalcType": "NESTED",
            "dimensions": [
              {
                "fieldCaption": "Region"
              },
              {
                "fieldCaption": "Segment"
              }
            ]
          }
        ]
      }
    ]
  }