2 Replies Latest reply on Aug 26, 2012 3:02 PM by Joe Mako

    Filters and Custom values

    manideepak ganapathi

      A particular variable A is calculated as a weighted average using variables B and C (weight) . Now, the data is at two levels Region (North America, Latin America etc.) and Country (Australia, US, India etc.). I have created this field A as a calculated field using the following formula:

       

      Sum(B*C)/ Sum(C)

       

      If I create a quick filter for just region and select a region say region 1, the value of A will be aggregated based on the B and C values for all the countries in the selected region (region 1).

       

      The problem is, when an user selects the region filter as (All), which is nothing but all the regions or the worldwide level, the calculation of variable A should be designed differently. Now that I have A values for individual regions which are calculated using variables B and C, I want to create the value of A at worldwide level using the following formula

       

      A(region 1)*0.2 + A(region 2) *0.25 + A(region 3) *0.4+ ...

       

      where,

      A(region i) is value of A calculated for region i

      0.2, 0.25, 0.4 can be giveN as user inputs or manually into a formula

       

      Would appreciate a quick reply.

       

      Thanks,

      ManiDeepak

        • 1. Re: Filters and Custom values
          Tracy Rodgers

          Hi Manideepak,

           

          One suggestion might be to define the calculations as desired, then create a parameter that changes which calculation is referenced.

           

          Or to use a parameter as the filter to determine which region is present and which is not.

           

          Hope this helps a bit!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Filters and Custom values
            Joe Mako

            How about a calc field like:

             

            IF MAX([Region])=MIN([Region]) THEN

              SUM([Value]*[Weight])/SUM([Weight])

            ELSE

              SUM(IIF([Region]="A",[Value]*[Weight],0))/SUM(IIF([Region]="A",[Weight],0))*.10+

              SUM(IIF([Region]="B",[Value]*[Weight],0))/SUM(IIF([Region]="B",[Weight],0))*.15+

              SUM(IIF([Region]="C",[Value]*[Weight],0))/SUM(IIF([Region]="C",[Weight],0))*.25+

              SUM(IIF([Region]="D",[Value]*[Weight],0))/SUM(IIF([Region]="D",[Weight],0))*.5

            END