I've attached a workbook to demonstrate a possible approach.
Here's the basic setup:
I'm going to have a calculation that gets the total number of selected platforms.
[Total # Selected Platforms]
Then I'll compare, per User the number of records with the number of platforms and keep only those that match. Notice above that the Total # of Selected Platforms is always 2. The number of records per user varies. User 2 is the only one that matches. By moving the calculation to the filters, I can keep only User 2 (when AMEX and Visa are the two selected).
Hope that helps!
Happy New Year!
Excluding Filters.twbx 15.1 KB
First of all, thanks very much for replying. I really appreciate the time you've dedicated on this.
Unfortunatelly, this approach is not working to my data. I've created the same calculated fields and the data I visualize doesn't match my criteria.
I was wondering why, when I realised that your approach doesn't work 100%. Case:
If I filter by PayPal & Visa I should visualize user 1 and user 4. And I only see user 4.
This happens because user 1 has paid three times, twice with Paypal and once with Visa.
The SUM([Number of records]) is higher than the # selected platforms.
SUM([Number of records]) = 3 and [Total # Selected Platforms] = 2.
I've solved that out changing the calculated field 'Keep User Who has All Selected' to: IF COUNTD([Platform]) == [Total # Selected Platforms] THEN 1 END
I've attached the workbook with the new formula. So, if anyone want to use that filter, use that formula.
This exercise is very useful to recognise which users have paid with different platforms (and with which platforms).
But, I'd like to be able to group the users, so I could work with these user segmentations to discover other info. And this is not possible using that approach.
What do you think about?
Do you have any other idea on how we could make this possible?
Excluding Filters.twbx 14.8 KB
How about using some sets for this kind of analysis. You could create a set for each type of user (e.g. AMEX Users or Visa Users) then you could create combined sets for the combinations you want to analyze (e.g Visa AND Amex Users).
The set would be a conditional set, using a formula to determine if the user had that type of card. Here's the setup for the original set:
Then to combine the sets, CTL + Click to select two of them and right click and select "Create Combined Set":
This will be all users who have Visa and AMEX (only User 2 in the sample data).
Then you can use the set to filter or show who's in and out of the set.
The IN/Out functionality is not available with data sources using the MS JET driver (Excel, Access, Text files) , but you can extract those if needed (also not available with OLAP).
Hope that gives an alternate approach that helps!
Excluding Filters.twbx 19.1 KB
Thank you very much. This really solves my issue.
Great! Glad to help!