Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!!
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.
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.
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))