Consolidating excel data

2.56K viewsDataLink
0

New to Quantrix and cutting my teeth on the following problem:-

a) Have a small number of excel files from unit managers, updated weekly, with sales forecasts in the form:-
projectId,projectName,status,month,year,value1,value2

b) I want to combine these into a time based matrix within Quantrix
dimensions: projectId, status, month, year with value1 and value2 as the item dimension

c) I can do sucessfully manually using datalink by:-
1) deleting the contents of the consolidation matrix back to just month/year dimensions
2) repeatedly editing and running the datalink for each for the spreadsheets

I need to have this run automatically, for example, to simply say “”DataLink/Update All”” to get my consolidation updated.

I have tried setting this up so that each of the unit excel files are imported as a flat matrix, which would allow me to “”Datalink/Update All”” to get new data replacing earlier data. What I cannot see this way is how to then pull these together into a single consolidated multi-dimensional matrix.

As a follow-on, we are will want to do the same with cost forecasts, pulling other measures from other sources together into the top level forcasting matrix.

(Datapull is not an option as current budgets won’t allow unit managers their own Quantrix)

Really appreciate any ideas.

0

Hi – In your Quantrix Application, choose Help > Sample Models and open the file “Data_Summarization_Techniques.model”. This demonstrates some functions that take 2D flat data and turns it into a multi-dimensional analysis. Give it a look and see if this helps.

-Mike