Regression Analysis on aggregated data

[Austin Fox]

I am trying to do regression analysis on aggregated data (both Tools->DataRelationships->LinearRegression and Tools->RegressionModeling). The only way I have been able to figure out how to do this is by creating an aggregated information link by first creating aggregated columns in the information designer. This is extreemly cumbersome and doesn't allow for easily changing the aggregation (which would be very useful). Is there another way to do this Maybe creating a calculated table or can you specify aggregation somehow in those analysis

Edit:

One caveat is that the data also needs to be joined from multiple tables.

Thank you,

Austin

[Shuting Fu]

Did you try to add a pivot transformation to your data table with the aggregations before doing the regressions

[Austin Fox]

I am trying that now. Is there a way to join data from multiple tables in a pivot table

[Shuting Fu]

You need to do the joins (adding columns/rows) before adding pivot transformation, or vise versa.

[Austin Fox]

Ok. So I think you have lead me to the answer. You can in fact pivot from multiple tables given a common unique identifier (UID or foreign key) to use as your row identifier. After creating the initial pivot table insert->columns. 'Add columns to:' the pivot table, 'Add columns from:'the additional table to pivot from. Then setup the pivot. After creating the new pivot select 'next' and match the UID (row identifier) with the one that already exist in the original pivot table.

[Shuting Fu]

Awesome process.