Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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

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

liviumac_1-1590826052393.png

 

 

 



Labels (1)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

Creator
Creator

 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!