9 Replies Latest reply on Sep 9, 2016 2:06 AM by Dhritul Gupta

# Filter a dimension by another dimension

Hi everyone,

In the attached workbook, I have a collection of customers who make either purchases (of products) or redemptions (of loyalty points) in a store. The same user can have redeemed and purchased multiple things.

What I'm looking to do is isolate and run analysis on the customers who have only redeemed for "vouchers"

I know that I can find the customers who have only redeemed for vouchers by putting the Redemption field in the filter, as I have done in the first sheet in the workbook.

But ideally I'm looking to somehow isolate these users, so I can investigate them as a distinct group without needing to get there in each sheet by adding all users and then filtering them down. It would be nice for instance to be able to create another viz and then think "I wonder how the voucher-redemption-only folks compare to this", and just drop them in with a single pill.

My instinct is a calculated field or a set running off a formula, but I'm afraid I'm a bit stuck.

• ###### 1. Re: Filter a dimension by another dimension

hi Samuel,

Probably the easiest way to do this is with sets. Using a very clever (btw I didn't come up with it, which is why it's clever!!) condition, we can tag (at the User ID level) if a user has used a Voucher or not.

So create a set, against User ID, with the following condition formula

MAX(IIF([Redemption Product] = 'Voucher',1,0))=1

as set condition, needs to be an Aggregate formula (that's why we use the MAX) and a Boolean test. From the 'Sets - SR' tab you can see that it is putting the Voucher customers in the set, and everyone else out. As this equated at Row level, we can also access this in a further formula if required

IIF([Voucher User IDs],'Voucher','Other')

Hope this helps, and makes sense, but if not please post back

btw this fantastic video, from TDT, explains exactly how this works

Think Data Thursday - November 20 - Let's talk about Sets Baby!

3 of 3 people found this helpful
• ###### 2. Re: Filter a dimension by another dimension

Hey Samuel,

How about defining a computed set 'voucher-redemption-only folks' based off User IDs who fulfill the condition:

MAX(iif ([Redemption Product]=="Voucher",1,0))==1

This basically evaluates on the transaction level and will mark with a 1 every voucher redemption transaction. At the higher level of user IDs, the condition will be true only if the user had at least one transaction of voucher redemption.

Then you can use the set to color/separate users who are in/out of the set, as well as show members of the set. I am attaching a workbook in version 9.

Hope this helps,

Simon was faster

2 of 2 people found this helpful
• ###### 3. Re: Filter a dimension by another dimension

...Lets go with 'Great Minds Think alike'!!

• ###### 4. Re: Filter a dimension by another dimension

Thanks very much guys, I really appreciate the excellent responses.

I've gone and watched the relevant part of the video you linked to Simon as well. Just one question left, if either of you have a moment to indulge me.

I understand that the set requires a boolean element, as a customer is either in or out. This is satisfied by the IIF. I also understand that the set requires an aggregate, because we're looking at row level data  and aggregating it up to the customer level, and this is satisfied by the MAX.

For a while though, the =1 at the end of the formula really confused me. I think I get it now, but just want to check.

Does the formula you supplied come wrapped in an implicit "if", because writing it into the formula area of the set dialogue is essentially writing a boolean condition for entry?

So when written into a set formula field:

MAX(IIF([Redemption Product] = 'Voucher',1,0))=1

is actually translated as:

(IF) MAX(IIF([Redemption Product] = 'Voucher',1,0)) =1 (THEN: Set membership is a go)

??

That seems to make sense, but I just want to check before forging ahead.

Thank you!

• ###### 5. Re: Filter a dimension by another dimension

hi Samuel,

Yes it took a bit of time for this to sink in with me! I've done an Excel Spredsheet showing how this works, but an currently unable to post this (we've had some spam attacks on the site, so spam filters [including attaching files] is set to Max!).

Hopefully you can see the Excel formula in the screen grab I've done..

So in Column F we have the Row Level formula, which is exactly the same as Tableau.

As this set is created at UserID level, the next part takes the Max of that Row Level calculation, and restarts it every new UserID.

It then says does this value = 1, if it does, put that UserID in the set

One other thing to note. Is that sets are based on the entire data set, so if you filter down by date, to a montn (for example) even if a member hadn't had a voucher redemption in that month they would still be in the set. The way to get round this, is to make that filter a 'context' filter. In that way, and how I think if them is that they work very much like the FIXED LoD calc, if you've had much of a play with these?

Hope this makes sense, but if not drop a note back here and I'll go into a bit more detail. I'll post up the excel document when the spam filters are relaxed!

• ###### 6. Re: Filter a dimension by another dimension

Hi Simon,

Thank you again, and apologies for the delay in my response.

That does makes sense, thank you. I have experience with Context filters (which AFAIK create a mini-table out of the main dataset and restrict all operations against that subset of data), and am slowly working my way through LOD calculations.

It's the aggregates that confuse me still - I get the feeling that once it clicks how Tableau relies and integrates aggregates into its structure, everything else becomes crystal clear.

Just working toward that slowly

• ###### 7. Re: Filter a dimension by another dimension

hi Samuel,

On way of thinking about Row Level calcs and Aggregate calculations, that's helped me...

Row Level calcs are, as you'd assume, calculation (very much like the ones Column F of my Excel post) run across every row of the data, only using that row's information to determine the result. as si are in the Tableau data

Aggregate Calculations, such as SUM([Sales]) aren't defined in the data, as you don't know what the result is, until Tableau knows the Level of  Detail you are requesting. i.e. Just drag in SUM([Sales]) and it's a single number, which is the sum of the entire Sales Column; now drag in Region (btw I'm thinking SuperStore data here!) and you have 3 values, which are the total for each of the region. This is a different number from the first example, so you can see how it can't exist in the data as a 'real' field.

Sets (built in the above way) and FIXED LoD calculations are the best of both worlds. They use more than just a single row to calculate their value, but are applied in the data to every row in the data.

btw this is just a way of thinking about things, and does not necessarily (certainly for sets and FIXED LoD) reflect what's really going on under the bonnet...just thought I'd get that caveat in before a Zen shoots me down!!

Hope this helps though...I can now also attach my Excel example, where I've re-created (in theory terms!) how a FIXED LoD works. As Excel doesn't have a MAXIFS function, I've used SUMIFS, but has the same affect here as we only get one true value per customer.

• ###### 8. Re: Filter a dimension by another dimension

Hi Simon,

I never came back to you on this - my apologies. Thank you again for your excellent help.

Sam

• ###### 9. Re: Filter a dimension by another dimension

Hi Everyone,

Although this post has been dormant for quite sometime I just figured I would contribute my findings as well.

Although all the above answers work as well I found a very simple solution for all of this.

I just created a calculated field with an if condition

IF ([Redemption Product]='Voucher')

THEN

[User ID]

END

so this new calculated field is a dimension which contains only those user ID's which have [Redemption Product]='Voucher'.