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
Tab Example - SR.twbx 55.9 KB
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
Tab Example.twbx 36.1 KB
...Lets go with 'Great Minds Think alike'!!
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.
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!
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
Glad it was of help.
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.
Set Condition.xlsx 9.4 KB
I never came back to you on this - my apologies. Thank you again for your excellent help.
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')
so this new calculated field is a dimension which contains only those user ID's which have [Redemption Product]='Voucher'.