8 Replies Latest reply on Mar 5, 2012 1:56 AM by Andrew Watson

    If with Aggregate

    Ricardo Mota



      I read several posts with this subject but I don't achieve the result:


      I have this calculated field:



      ([CodMarca] = "10129" or

        [CodMarca] = "10130" or

        [CodMarca] = "10147" or

        [CodMarca] = "10152" or

        [CodMarca] = "10153" or

        [CodMarca] = "10154" or

        [CodMarca] = "10158" or

        [CodMarca] = "10160" or

        [CodMarca] = "10161" )




      [Total Liquido]*[% Grupo A]




      ( [CodMarca] = "10134" or

        [CodMarca] = "10148" or

        [CodMarca] = "10150" or

        [CodMarca] = "10157" or

        [CodMarca] = "10162" )




      [Total Liquido]*[% Grupo B]




      ( [CodMarca] = "10138" or

        [CodMarca] = "10139" or

        [CodMarca] = "10149" )




      [Total Liquido]*[% Grupo C]





      And I need that [Total Liquido] it's a Aggregated. I Tried ATTR in [CodMarca] but does n ´t work.


      Any advice?


      Thank You

        • 1. Re: If with Aggregate
          Peter Hopwood



          Having looked at your example the calculation looks like its working [Rappel Calculado] seems to be multiplying the [Total Liquido] by the correct Parameter value for Group A, B and C (and not D as there is no parameter). There seem to be multiple [CodMarca] values to  [Marcas] but only one that ever has [Total Liquido] values. The Grand Totals look ok too.


          If I remove the [Marcas] value the aggregation to the groups look ok ?


          What result do you want to get?




          • 2. Re: If with Aggregate
            Ricardo Mota

            Hi Peter


            Thank you


            This is to calculate the amount of discount by group of brand (A, B and C). Each group as a different %.

            So I have to IF the several brands to say in what group they belong.


            In the end I want to SUM([Total Liquido]) + SUM([Rappel Ano Anterior]) and like this it turns in a Aggregated field.




            Thank you once again.

            • 3. Re: If with Aggregate
              Ricardo Mota



              I have found a solution but know I have a Grand Total Problem: It's not right. Sub Totals work well but not Grand Totals.


              The solution is:



              (INT( MIN ([CodMarca])) = 10129 or

                INT( MIN ([CodMarca])) = 97129 or

                INT( MIN ([CodMarca])) = 10130 or

                INT( MIN ([CodMarca])) = 10147 or

                INT( MIN ([CodMarca])) = 10152 or

                INT( MIN ([CodMarca])) = 10153 or

                INT( MIN ([CodMarca])) = 10154 or

                INT( MIN ([CodMarca])) = 10158 or

                INT( MIN ([CodMarca])) = 10160 or

                INT( MIN ([CodMarca])) = 10161 )




              [Valor Total Liq.]*[% Grupo A]







              [Valor Total Liq.] it's a aggregated.



              • 4. Re: If with Aggregate
                Tracy Rodgers

                Hi Ricardo,


                It is important to know that grand totals are not necessarily are sum of the values in the rows/columns. Rather, they use whatever the aggregation is on the measure. So for example, if max is the aggregation being used on a measure, the grand total will be the largest value in the column/row, not a sum of the max values. This is a great suggestion for the idea board--allowing the user to decide how the grand total should be aggregated.



                1 of 1 people found this helpful
                • 5. Re: If with Aggregate
                  Ricardo Mota

                  Hi Tracy


                  Thanks !


                  So I need to get back to the Beginning of my problem:


                  I Want to do IF with some Aggregate and some none Aggreagate:


                  IF [PRODUCT] = "10101" or [PRODUCT]= "10102" THEN SUM(something) * [ParameterA]

                  ELSE IF [PRODUCT] = "10103" or [PRODUCT]= "10104" THEN  SUM(something) * [ParameterB]


                  How can I do That in a Calculated Field?


                  Thank you very Much

                  • 6. Re: If with Aggregate
                    Andrew Watson

                    Have you tried something like:


                    sum(IF [Product] = 10101 or [Product] = 10102 then [Something] * [ParameterA] else [Something] * [ParameterB] end )



                    • 7. Re: If with Aggregate
                      Ricardo Mota

                      Hi Andrew


                      Thank you, but it doesn't work.


                      I Tried MIN(INT([Product] = 10101)) ...

                      and with SUM, MAX, ATTR and it works on some Products (not all)  but in Grand Totals the results aren't correct.



                      • 8. Re: If with Aggregate
                        Andrew Watson

                        With regards to your Grand Totals the grand total uses the same aggregation as in your calculated field. For example if you use a MIN aggregation the grand total will show the MIN of your selection, if you use a MAX it'll show the MAX, if you choose SUM it'll sum the selection, etc.


                        It doesn't make sense that what you're seeing works for some products but not for others. Perhaps if you attach the workbook and a brief description of what you're doing and where it fails it'll help with finding a solution.


                        Before sending this I just had a read higher up the thread and realised Tracy said the exact same thing about the grand totals.