IDSB Banner

Pivoting Data with Quantrix: Two Useful Techniques

When working with large two dimensional data sets, it can be very useful to 'slice' or 'pivot' the data to gain insights into the modeling problem at hand. With a Microsoft Excel ® spreadsheet, this task becomes difficult with larger datasets. Also, while spreadsheet pivot tables are useful for reporting, they are not easily modified to perform additional calculations on the data. With Quantrix, you can solve both issues at the same time. Quantrix can handle an unlimited number of rows.  Also, once you turn the flat data into multi-dimensional presentations, additional formulas and calculations can easily be applied to the model.

In this example, we are analyzing a data set of 87,000+ rows from the United States Census Bureau. The data contains population estimates for the year 2004 by State, Region, Regional Division, Sex, Race, Age and Age Range. The task is to ‘pivot’ or turn this data into different multi-dimensional data presentations and to perform calculations on this data. It is quite easy to quickly generate three different slices of this single data set from within a single model.

By Sex & Age via DataLink Using Quantrix DataLink technology, a DataLink can be established against the matrix that contains the two-dimensional source data. By defining the Sex and Age Range columns as categories and adding a couple of formulas, one can quickly determine the Male / Female ratio by Age Group. It becomes immediately apparent that as the population ages, the percentage of females that make up the age segment increases rapidly. Click on the By Sex & Age matrix and then choose Tools > DataLink > Edit DataLink to see how the DataLink import is constructed.

By Age & Division via DataLink Using the same matrix that contains the two dimensional source data, a new DataLink can be established to analyze the Age Group by Regional Division. This analysis is useful to illustrate how the population shifts to different regions of the country as the population ages.

Click on the By Age & Division Matrix and then choose Tools > DataLink > Edit DataLink to see how the DataLink import is constructed.

By Sex & State via 'Using As'
  The 'Using As' formula statements allow you to very easily ‘slice’ large data sets into specific summary presentations. In this example, the task is to see the Male / Female breakdown by specific states. While DataLink allows you to analyze all the states, 'Using As' formula statements allow you to analyze just the target data in a user-defined matrix. Specifically, by setting up a new matrix with a State category that contains only the state items needed, and another category with the Male and Female sex labels, the following formula can be used:

Population = sum(Source Data::POPESTIMATE2004) using Source Data::STATE as State, Source Data::SEX as Sex

This formula is similar to the SELECT function, but has the advantage that it can easily slice across multiple categories. Thus, the formula indicates that the Population Estimate values in the Source Data matrix are going to be summed by State, and by Sex using the State and Sex values in the current matrix.

In one Quantrix model, three different multi-dimensional presentations have been created. At 700,000 cells, a file size of 959 KB and only 150KB of memory, there is plenty of room for additional multi-dimensional analysis in the model.


    >>Pivot Sample Model

About Quantrix - Contact - Site Map - Refer A Friend - Support
Solutions - Products - Services - Community - Partners - Webinars - Learning - Store
© Copyright 2014 Subx Inc.-All rights reserved -+1.207.775.0808 -