0 Replies Latest reply: Apr 17, 2015 4:04 PM by Allison Bruno Matos Baldan RSS

    Calculating values based on selection



      So, when I select something on Filter01, a set of values are also selected in Filter02. I have a table where a see all values that are on Filter02 in list. Looks something like this:



      Filter02 | Area | Filter01 Productivity  | Filter02 Productivity

      Name1     30                                                 60,2

      Name1     33                                                 34,2

      Name1     43                                                 40,0

      Name2     21                                                 55,3

      Name2     22                                                 71,7


      So, for example: the Name1 is correspondent of Filter01, and he has productivity on many areas like above, he had productivity on area 30, 33 and 43. What I need is to calculate the Filter01 productivity in the same areas that Name1, Name2, etc... (those who got selected on Filter02 when I select something on my Filter01).


      Basically, if Name1 had productivity in areas 1, 10, 11, 23, 35, I need to show the productivity of Filter01 on those areas too.


      I have a code working, but I'm calculating each area separated and since I have 100+ areas, that's not viable.

      Here what I have working, but as you guys can see, each area have one if, so the code will be pretty long.

      If you guys can help me simplificate this code I would apprecciate.


      if(Filter02 <> Filter01,

        if(Avg(Productivity) > 0,

      if(Only(Area) = 21, Avg(TOTAL {<Filter02={'$(=Only(Filter01))'}, Area = {21}>} Productivity),

      if(Only(Area) = 22, Avg(TOTAL {<Filter02={'$(=Only(Filter01))'}, Area = {22}>} Productivity),

      if(Only(Area) = 30, Avg(TOTAL {<Filter02={'$(=Only(Filter01))'}, Area = {30}>} Productivity),

      if(Only(Area) = 33, Avg(TOTAL {<Filter02={'$(=Only(Filter01))'}, Area = {33}>} Productivity,

      if(Only(Area) = 43, Avg(TOTAL {<Filter02={'$(=Only(Filter01))'}, Area = {43}>} Productivity))))))))



      Allison BMB