Calculating values based on selection

Fri, 17 Apr 2015 20:04:22 GMT

Hello,

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:

Table

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))))))))

Regards

Allison BMB

topic Calculating values based on selection in QlikView

Calculating values based on selection