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

johnmorgan — Sat, 16 Nov 2024 03:12:10 GMT

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.

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

rwunderlich — Thu, 20 Feb 2020 23:41:25 GMT

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

topic Re: Aggregate Calculation Based on Value Inclusion in Smaller Set in a Sheet Expression in App Development

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

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