Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to find an expression to aggregate values based on a field in the values existence in another set.
For example:
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.
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