Convert an Analytica multidimensional array into an Excel PivotTable

The problem

While handling multidimensional data in Analytica presents minimal hurdles, exporting this data to alternate platforms such as Excel can prove to be less straightforward. The complexity escalates further when formatting adjustments are necessary to facilitate PivotTable transformation.

The solution

Using MdArrayToTable() without any optional arguments produces a single value column. This format easily pivots into an Excel pivot table and can be done in under 30 seconds.

1) Find the data you want to bring into Excel

MultiD Pivot Image 1.png

2) Create a new node to flatten your data. In the definition, call MdArrayToTable() and include your data as an argument. For above example this would look like MdArrayToTable(Travel_Costs)

MultiD Pivot Image 2.png

3) In your new object window, click in the top left gray box to highlight everything. Then, either right-click and press “Copy” or press CTRL+C

MultiD Pivot Image 3.png

4) Paste the data into Excel

MultiD Pivot Image 4.png

5) With the data still highlighted, navigate to the ribbon (top row), select “Insert,” and then select “PivotTable”

MultiD Pivot Image 5.png

6) Keep all default PivotTable settings and select “OK”

MultiD Pivot Image 6.png

7) Select your PivotTable Fields

MultiD Pivot Image 7.png

And that's it! For more on MdArrayToTable() and related functions, see related pages below.

See Also


You are not allowed to post comments.