4 Replies Latest reply: Jul 17, 2014 7:04 AM by Marcus Malinow RSS

    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.

        • Re: Reuse a formula
          David Pertegal

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

            • Re: Reuse a formula

              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.

                • Re: Reuse a formula
                  David Pertegal

                  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.

              • Re: Reuse a formula
                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))