Aggregate Calculation Based on Value Inclusion in Smaller Set in a Sheet Expression

[johnmorgan]

I am trying to find an expression to aggregate values based on a field in the values existence in another set. 

For example:

• a user has multiple identifiers
• I have a table of multiple users
• I have a table of the identifiers related to my user
• I have another table of identifiers
• those identifiers link to ownership of a record. 

I need to count the total distinct records belonging to my current user.  In the same application I need to get aggregates across the rest of the data so I do not want to use a filter or section access to restrict the data.

So basically I am looking  for something that will count distinct based on the intersection of a set with a smaller set.  More succinctly in SQL I would write Select count(distinct record) from record join owners where ownerIdentifier in (list of identifiers)

As example data say I have:

CurrentUserIdentifiers

1111

2222

Identifiers	ValueDataId
1111	12345
1111	23456
2222	34567
2222	45678
3333	56789

ValueDataId	D1	D2	...	...
12345	qwer	poiu
23456	asdf	nmhj
34567	zxcv	hjkl
45678	bvcx	yuio
56789	gfds	trew

And I would like to count the distinct D2 values for the current user in a single expression, without limiting other available data.

[rwunderlich]

If you need just a total for all the CurrentUserIdentifier values, use:

count({<Identifiers=P(CurrentUserIdentifiers)>}DISTINCT D2)

If you need to count perCurrentUserIdentifier I recommend loading "CurrentUserIdentifier as Identifiers"  to create a link. 

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