Summarizing Data Based on Multiple Criteria
Is there a function, or combination of functions, that will summarize data from a two-dimensional matrix based on multiple criteria? For example, the 2-D matrix contains Purchase Orders for various products to be delivered to various locations in various months. I want to summarize into another matrix that has categories of Product, Delivery Location, and Delivery Month with items representing the total number of orders and the total dollar value. So, the end result tells me how many orders (and the total sales value) I have on the books for every combination of Product, Month, and Location.
Hi Craig – Thanks for your post:
I recommend exploring using an inter-matrix DataLink to achieve this. Basically, you can use an existing 2D matrix in Quantrix as a source file for a DataLink process.
Therefore, you can specify the Month, Location and Product columns as categories in the new matrix, and the information is appropriately categorized and summed if need be. If new data is introduced into the 2D matrix, simply choose Tools > DataLink > Update to get the new data into the Multi-D Matrix.
While this is not a formulaic solution, it is worth consideration. It is demonstrated in the attached model. Please take a look and post again if any questions arise.
-Mike