Discussion Board for collaboration related to Creating Analytics for QlikView.
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.
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
Migration 50 26 20
Creation 48 29 31
Resiliation 26 5 51
CT 3: The formula is (CT2 - CT1)/2.6
Is it possible to reference CT2 and CT1 in CT3 ? (The formula)
I'd do it this way (1 table):
load * inline [
Date, Migration, Creation, Resiliation, Claim_type
Migration as Migration_treated,
Creation as Creation_treated,
Resiliation as Resiliation_treated
resident CT1 where Claim_type='Treated'
left join load
Migration as Migration_received,
Creation as Creation_received,
Resiliation as Resiliation_received
resident CT1 where Claim_type='Received'
(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
drop table Treated;
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:
'Treated' as Claim_type
drop table CT1;
'Received' as Claim_type
drop table CT2;
resident new_CT1 where Claim_type='Treated'
resident new_CT1 where Claim_type='Received'
cheers, if it's not clear you can attach a sample of your model.
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
Margin = Sum($(vCalcTotalSales) - $(vCalcCostOfSales))