4 Replies Latest reply: Feb 16, 2011 12:35 PM by P.Schmerbach RSS

    Excluding and including data for an aggregation

    P.Schmerbach

      Hey there,

      I'm trying to work out a pretty easy formula for a pretty easy value:

      I have a company dimension, which holds legal entities with their respective groups:

      example: Group 1 belongs to 3 entities - the whole information is saved in one table.

       

      Now my problem:

      I want to build something like a gauge-based KPI - which shows the actual summarized year sales amount for the selected lega entity in relation to it's groups sales amount:

      SUM(Selected Entity Sales Amount)/(SUM(Group Sales Amount of selected entity)/COUNT(entites of group)

       

      Any ideas?

       

       

       

       

        • Excluding and including data for an aggregation
          Miguel Angel Baeyens de Arce

          Hello,

          Have you tried using TOTAL <Field> ?

           

          Sum(Amount) / Sum(TOTAL <EntityID> Amount)


          Hope that helps.

            • Excluding and including data for an aggregation
              P.Schmerbach

              I've tried TOTAL as well but it didn't turn out as what I wanted it to be :)

              It's looks like TOTAL is also affected by the selection in the field list, as it allways shows me 100%.

              But that's not what I want it to show.

              I want it to compare the Sales Amount of the selected Entity with the avg sales amount for the whole group, where 100% is ok, 75% is bad and 100+% is good, by now the scale is all I could manage :D

              Is there a way to make objects inside a dashboard "independent", so they will only be effected by some, not all selections. In my case I want that all selections will be ignored except the company dimension.

                • Excluding and including data for an aggregation
                  Miguel Angel Baeyens de Arce

                   


                  P.Schmerbach wrote:Is there a way to make objects inside a dashboard "independent", so they will only be effected by some, not all selections. In my case I want that all selections will be ignored except the company dimension.


                  There are ways to keep your values agains the whole data. If TOTAL gets all values in expression, set {1} means all values in dimension, so combined should return what you want regardless the selection:

                   

                  Sum(Sales) / Sum({1} TOTAL Sales)


                  Hope that helps.

                   

                    • Excluding and including data for an aggregation
                      P.Schmerbach

                      Your suggestion will give me the overall total sales summarized... but thanks for your help :)

                      By now I found a solution for my problem... it's really hard implementing some "easy" business logic steps into this nice point & click adventure :D

                      My solution looks like this:

                       

                       

                      IF

                       

                       

                      (((SUM({1<Client=P({$<Client>} )>} Umsatz)-Sum(Umsatz))<>0),Sum(Umsatz)/((SUM({1<Client=P({$<Client>} )>} Umsatz)-Sum(Umsatz))/COUNT(TOTAL CompanyKey

                      )), 0)