4 Replies Latest reply: Sep 9, 2011 8:04 AM by Stefan Wühl RSS

    Different calculation for Total

      Hi,

       

      I have taken over a customer with a straight table with different KPI' s. The problem is that I need a different calculation for Total.

      I have a circular group fro dimension, with for example Segment and ='Total'. And they want if they choose Total the expression to read from another table.

      There's 8 expression today that looks like (modified):

       

      -(
      -(sum( {$<RR_A={'Insurance'}, RR2_ReportRow={'A'}>} RR_Amount ) /
      sum( {$<RR_A={'Insurance'}, RR2_ReportRow={'D'}>} RR_Amount ))
      +
      (-(sum( {$<RR_A={'Insurance'}, RR2_ReportRow={'C'}>} RR_Amount ) /
      sum( {$<RR_A={'Insurance'}, RR2_ReportRow={'D'}>} RR_Amount )))
      )
      -
      (-sum( {$<RR_A={'Insurance'}, RR2_ReportRow={E'}>} RR_Amount ) /
      sum( {$<RR_A={'Insurance'}, RR2_ReportRow={'D'}>} RR_Amount ))

       

      The problem is if they choose Total the expression must look like as above BUT with RR1_ReportRow instead of RR2....

       

      Is there a way to achieve this?

       

       

      @Ungvall

      Senior Business Discovery Manager at Advectas AB

        • Different calculation for Total
          Stefan Wühl

          I think you could use something like

          =if(dimensionality()=0, EXPRESSIONTOTAL, EXPRESSION)

           

          as expression and replace the two EXPRESSION... with your expressions for expression in rows and total.

           

          You should enable partial sums (for your top dimension) in presentation tab, of course.

           

          Hope this helps,

          Stefan

            • Different calculation for Total

              Hi,

               

              I tried this but get an error in calcultated dimension...

               

              =if(dimensionality()=0,
              (-(-(sum( {$<RR1_ReportRow={'A'}>} RR_Belopp ) /
              sum( {$<RR1_ReportRow={'D'}>} RR_Belopp ))
              +
              (-(sum( {$<RR1_ReportRow={'B'}>} RR_Belopp ) /
              sum( {$<RR1_ReportRow={'D'}>} RR_Belopp )))
              )),
              (-(-(sum( {$<RR_Affär={'Försäkring'}, RR2_ReportRow={'A'}>} RR_Belopp ) /
              sum( {$<RR_Affär={'Försäkring'}, RR2_ReportRow={'D'}>} RR_Belopp ))
              +
              (-(sum( {$<RR_Affär={'Försäkring'}, RR2_ReportRow={'B'}>} RR_Belopp ) /
              sum( {$<RR_Affär={'Försäkring'}, RR2_ReportRow={'D'}>} RR_Belopp )))
              )
              -
              (-sum( {$<RR_Affär={'Försäkring'}, RR2_ReportRow={'E'}>} RR_Belopp ) /
              sum( {$<RR_Affär={'Försäkring'}, RR2_ReportRow={'D'}>} RR_Belopp ))))

               

               

              @Ungvall

              Senior Business Discovery Manager at Advectas AB

                • Different calculation for Total

                  And...

                   

                  The calculation for every expression is different, so I can't use one calculation for Total...

                   

                   

                  @Ungvall

                  Senior Business Discovery Manager at Advectas AB

                  • Re: Different calculation for Total
                    Stefan Wühl

                    Sorry, I missed your point with the group dimension, my solution is for use with expression / partial sums total.

                     

                    But I think you could use

                    GetCurrentField( groupname )

                     

                    to check for the field dimension in your calculated dimension expression.

                     

                    Regards,

                    Stefan

                     

                    Edit:

                    Maybe like

                    =if(GetCurrentField( groupname )<>'Segment',

                    (-(-(sum( {$<RR1_ReportRow={'A'}>} RR_Belopp ) /

                    sum( {$<RR1_ReportRow={'D'}>} RR_Belopp ))

                    +

                    (-(sum( {$<RR1_ReportRow={'B'}>} RR_Belopp ) /

                    sum( {$<RR1_ReportRow={'D'}>} RR_Belopp )))

                    )),

                    (-(-(sum( {$<RR_Affär={'Försäkring'}, RR2_ReportRow={'A'}>} RR_Belopp ) /

                    sum( {$<RR_Affär={'Försäkring'}, RR2_ReportRow={'D'}>} RR_Belopp ))

                    +

                    (-(sum( {$<RR_Affär={'Försäkring'}, RR2_ReportRow={'B'}>} RR_Belopp ) /

                    sum( {$<RR_Affär={'Försäkring'}, RR2_ReportRow={'D'}>} RR_Belopp )))

                    )

                    -

                    (-sum( {$<RR_Affär={'Försäkring'}, RR2_ReportRow={'E'}>} RR_Belopp ) /

                    sum( {$<RR_Affär={'Försäkring'}, RR2_ReportRow={'D'}>} RR_Belopp ))))