Reuse a formula

[#1]

Hi,

I've created 2 crosstabs with same dimensions, two dimensions exactly. I would like to create an another crosstab where the expression depend of the two last expressions.

Eg:

CT 1: Claims Treated

Type : Date 07/07/2014   08/07/2014  09/07/2014

Migration          8                10              5

Creation           5                 20              16

Resiliation         51                0               2

CT 2: Claims Received

Type : Date 07/07/2014   08/07/2014  09/07/2014

Migration          50                26              20

Creation           48                 29              31

Resiliation         26                5               51

CT 3: The formula is (CT2 - CT1)/2.6

Type : Date 07/07/2014   08/07/2014  09/07/2014

Migration                                      

Creation                                         

Resiliation                                     

Is it possible to reference CT2 and CT1 in CT3 ? (The formula)

Thanks.

[#2]

I'd do it this way (1 table):

CT1:

load * inline [

Date, Migration, Creation, Resiliation, Claim_type

07/07/2014,8,5,51, 'Treated'

08/07/2014,10,20,0,'Treated'

09/07/2014,5,16,2, 'Treated'

07/07/2014,50,48,26, 'Received'

08/07/2014,26,29,5,'Received'

09/07/2014,20,31,51, 'Received'

];

Treated:

load

Date,

Migration as Migration_treated,

Creation as Creation_treated,

Resiliation as Resiliation_treated

resident CT1 where Claim_type='Treated'

;

left join load

Date,

Migration as Migration_received,

Creation as Creation_received,

Resiliation as Resiliation_received

resident CT1 where Claim_type='Received'

;

load Date,

(Migration_received-Migration_treated)/2.6 as Migration,

(Creation_received-Creation_treated)/2.6 as Creation,

(Resiliation_received-Resiliation_treated)/2.6 as Resiliation,

'Received-treated/2.6' as Claim_type

resident Treated;

drop table Treated;

cheers!!

[#3]

CT1 and CT2 are created during analysis, that is, based on data already loaded.

The CT1 and CT2 I took for example are just a result obtained after applying formula on initial loaded data.

[#4]

But you can modify your CT1 and CT2 to be in one table adding the field Claim type or something similar, am I right?.

Is better than having two tables. If you have two tables with the same name fields provoque syntetic keys that is bad for the model performance.

something like this:

new_CT1:

load

Date,

Migration,

Creation,

Resiliation,

'Treated' as  Claim_type

resident CT1

;

drop table CT1;

load

Date,

Migration,

Creation,

Resiliation,

'Received' as  Claim_type

resident CT2

;

drop table CT2;

Treated:

load

Date,

Migration as Migration_treated,

Creation as Creation_treated,

Resiliation as Resiliation_treated

resident new_CT1 where Claim_type='Treated'

;

left join load

Date,

Migration as Migration_received,

Creation as Creation_received,

Resiliation as Resiliation_received

resident new_CT1 where Claim_type='Received'

;

load Date,

(Migration_received-Migration_treated)/2.6 as Migration,

(Creation_received-Creation_treated)/2.6 as Creation,

(Resiliation_received-Resiliation_treated)/2.6 as Resiliation,

'Received-treated/2.6' as Claim_type

cheers, if it's not clear you can attach a sample of your model.

[marcus_malinow]

Hi,

one approach I often take is to define my calculations in variables, and refer to these in my expressions.

So, a very trivial example

vCalcTotalSales = Sales

vCalcCostOfSales = Cost

In  my expressions

Sum($(vCalcTotalSales))

Sum($(vCalcCostOfSales))

Margin = Sum($(vCalcTotalSales) - $(vCalcCostOfSales))