Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Reuse a formula

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.

4 Replies
Not applicable
Author

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!!

Not applicable
Author

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.

Not applicable
Author

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
Partner - Specialist III
Partner - Specialist III

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))