Display a calculated measure based on a higher dimension than that shown in a table

[liviumac]

hello

probably the title does not say much, but here is my problem, sorry if it has been posted already by someone else

I have the following data structure: Client id, Client Region, Salesperson, Sales 

I need to display Sales calculated at the Region level in a table which only shows a subset of the clients - and this subset is defined by a selection on the Salesperson field

I could not manage to produce a formula that works in a predictable manner.

I also tried creating a separate table storing only region and region sales, but for some reason that also does not work correctly... maybe because the connection is via the Region field and not the client field, I don't know.

a solution is to use an apply map, but it is impractical for me (the data structure is much more complicated and I would have to use a compound key etc) so I was wondering whether a formula exists

thank you
LM

example mock data

[rwunderlich · Accepted solution]

I believe the expression you want is: 

sum(aggr(nodistinct sum({<Salesperson=>}Sales), Region))

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[liviumac]

 thank you kind sir!

so I understand that {<Salesperson=>} ignores selections for Salesperson, but may I please ask what is the purpose of nodistinct?

I've noticed that I get the same results without it, but also with distinct instead of nodistinct so this means you put it there to account for a situation which is actually not present in my mock data - and I would like to know what that situation is, as it is probable that it is present in my real life data.

thank you!