Display Excluded or List Only Impossible Values?

[#1]

I may have searched the entire internet looking for this, and I can't believe I'm the first to try this, but I can't find anything about it...

My Fact Table attaches a User ID to every activity record. Those activity records also have an Activity Date on them. This Activity Date links to my Calendar Table. My Calendar Table has a Date Code as well, which links to my Possible Users Table, which includes all of my company's User IDs. Like so:

Fact Table      |      Calendar Table      |      Possible Users Table

Fact ID

User ID

Activity Date   |      Activity Date

                       |      Date Code             |      Date Code

                                                            |      Possible User ID

Possible Users lists every ID active in the company for each day, so as date selections are made, the potential target audience is recalculated for the selected time period. count(distinct User ID) gives me my users, count(distinct Possible User ID) gives me my target demographic.

I'm trying to make a list, tablebox, or straight table chart that displays a static list of only the Possible User IDs that do not exist in User ID. If I leave the dashboard unfiltered, it displays potential users in my company that have never used our service. If I select the last 6 months, for example, it lists users that haven't been active in that time period.

Thing is, I have no idea how to do this. I've been banging my head against if for hours straight and tried many an expression, set analysis, &c. and nothing is working. I can make 2 listboxes, one for User ID and one for Possible User ID, select all User ID, and scroll down to the grey in Possible User ID and they're all right there as expected, but I can't seem to access them any other way.

Naturally, this is much over-simplified. There are many more pieces to the data model, and Because Reasons, changes to the data model are not a viable option. Things are the way they are and need to stay that way. I'm looking for a Presentation Solution, not a Script Solution.

[sunny_talwar]

Have you tried something like this as your list box expression:

Aggr(Only({1<[Possible User ID] = e([User ID])>} [Possible User ID]), [Possible User ID])

UPDATE: Added the missing curly bracket

[#3]

Yeah, the E() doesn't work with {1<>}, because nothing is excluded, so all possible IDs return "-". If you change that to  {$<>}, it works (sort of), but only when an ID is selected somewhere else, and even then I get a mix of results: some IDs with a matching Possible ID in white, some with a "-" possible ID still in white, and some with a "-" possible ID in grey.

[sunny_talwar]

Try this:

=Aggr(Only({$<[Possible User ID] = e({<[Possible User ID] = {$(=Concat(DISTINCT {1} [User ID], ', '))}>})>} [Possible User ID]), [Possible User ID])

[Peter_Cammaert]

Sunny's first suggestion works like it should (both with 1 and without). At least in my (simplified) test QVW. See attachment.

There's probably something in your document that doesn't like what you're trying to do. Can you post your document in this thread? Reduce the number of rows, or scramble the field content if you don't want to put your data in a public forum.

Best,

Peter

PS I cannot get his second suggestion to work (last listbox on the right). Sorry Sunny.

[Edit] Removed the attachment. See version below. Contains substantial improvements, thanks to Sunny T.

[sunny_talwar]

Hey Peter, thanks for looking into both my expressions. I have few quick comments here

1) I am not 100% certain, but the 1st expression do not show anything without a selection.

2) I think the possible list of user id include more names that user id list. And if the previous statement is true than you will see that the second expression works

[Peter_Cammaert]

Hi Sunny,

1. That's logical because in my example, when nothing is selected the UserID list contains all PossibleIDs which results in an empty exclusion list.
2. Whenever the PossibleID list contains more IDs than the largest UserID list, then the expression stops working. But that's the nature of the expression: it starts by calculating the UserID entries that haven't been activated. But the excess PossibleID entries don't have any records in the Facts table, so how can this work? It can't.

But then, for regular selections your expression 1 still works and your second expression doesn't (as far as I can see. Correct me if I'm wrong). Do you want Michael to put both listboxes on the same sheet?

I guess there is a simple solution to this. Add dummy records in the facts table for all PossibleIDs that aren' present in the Facts table from the start. Leave the Activity Date at NULL (and every field that may be used in a list box too). These are so-called zero-records. Didn't test it though. Wait a moment... Nah, doesn't work. New idea though...

[Peter_Cammaert · Accepted solution]

I think I got it. Turned everything upside down (I was thinking about set operators from the beginning and these prove - again - very valuable)

=Aggr(Only({<[Possible User ID] -= p([User ID])>} [Possible User ID]), [Possible User ID])

[Edit] Switched expression back to using Michael's field names instead of mine. Sorry about that...

[sunny_talwar]

Good idea but I am still not sure if he wants to always see the list of possible user IDs which are not available in user ID or if it needs to be driven by selection . I guess Michael is in the best position to determine what he is looking for, but my second expression will always show Peter, Sunny regardless of any selection, or at least that's what I wanted it to do.

[Peter_Cammaert]

You could be right, and I agree with Michael having the last say (of course). But this is what he said about his ideal listbox:

"If I select the last 6 months, for example, it lists users that haven't been active in that time period."