Who purchased both Product A and Product B

I got smilar questions couple of times regarding the issue I stated as title.

The below link is a case "Akiyah" tried to create VIZ with 4 calculated fields with using LOD calculations.

akiyah.hatenablog.com

But as he staetd in the contents, it's little bit troublesome to create 4 fields.

So, I will propose different approach.

 

First of all, create new calculated field to categorize customers.

 

Create Calculated Field 

[Customer Category]

if
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)}>0
and
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)}>0
then
"1-Purchased both "+[Param 1]+" and "+[Param 2]

elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)}
=
{fixed[Customer Name],[Order month] :sum([Sales])}
then "2-Purchased "+[Param 1]

elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)}
=
{fixed[Customer Name],[Order month] :sum([Sales])}
then "3-Purchased "+[Param 2]

ELSE
"4-No purchase on "+[Param 1]+" nor "+[Param 2]
END

 

Little bit long calculation??  That's right. I add some explanations.

[Customer Category]

if
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)}>0 // in same month, same customer, sum up Param 1's sales and judge it is gerater than 0

and
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)}>0 // in same month, same customer, sum up Param 2's sales and judge it is gerater than 0
then
"1-Purchased both "+[Param 1]+" and "+[Param 2] // if both of above two conditions are met, the text shows up.


elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 1] then [Sales]end)} // in same month, same customer, sum up Param 1's sales
=
{fixed[Customer Name],[Order month] :sum([Sales])} // in same month, same customer, sum up ALL sales
then "2-Purchased "+[Param 1] // in case above two matches each other, the text shows up.  that means only showing sales when only Param-1 are purchased.

elseif
{fixed[Customer Name],[Order month]:sum(if[Product Category]=[Param 2] then [Sales]end)} // in same month, same customer, sum up Param 2's sales
=
{fixed[Customer Name],[Order month] :sum([Sales])} // in same month, same customer, sum up ALL sales
then "3-Purchased "+[Param 2] // in case above two matches each other, the text shows up.  that means only showing sales when only Param-2 are purchased.

ELSE
"4-No purchase on "+[Param 1]+" nor "+[Param 2] // all of other cases than above
END

 The reason I add 1 to 4 on top of text is forcing to sort the category as alphabetical sorting, kind of TIPS, bit essential.

We put this calculated field on color shelf and get the view. Plus put this field on Label as well and we are able to see the category without color legend.

The benefit of this approach is that we can show the category name directly rather than show label by parameter name.

Here is the VIZ.

f:id:skn_0410:20160701062004p:plain

Yet, we feel a little bit unsatisfied ????

Because we want to highlight the category of "1- purchased both", but the VIZ does not meet that needs well.

So, additional works needed.

 

1. Highlight by Color

Emphasize "1- purchased both" means less emphasize others, then we change the color littel but lighter.

f:id:skn_0410:20160701054407p:plain

Double click on the red color of the category of "1- purchased both".

Then pick darker color.

f:id:skn_0410:20160701054550p:plain

f:id:skn_0410:20160701054723p:plain

f:id:skn_0410:20160701054824p:plain

Please ignore the preference of color for now, anyway, getting better.

2. Highlight by Label

 It's not bad, but we are always greed to Tableau and want to emphasize the label as well.

We want to make one category's lable size larger.

This is not doen thru standard function and need some trick.

To make that happen, we need to create two more calculated fields only for this purpose. In this case, the formula should be very easy.

 

[HighLight]

if [Customer Category] = "1-Purchased both "+[Param 1]+" and "+[Param 2]
then [Customer Category] end

 

[Non-HighLight]

if [Customer Category] <> "1-Purchased both "+[Param 1]+" and "+[Param 2]
then [Customer Category] end

 

Put both fields on Label shelf

f:id:skn_0410:20160701055404p:plain

and open detail setting window.

f:id:skn_0410:20160701055845p:plain

Remove break line, select only <HighLight>, and change the format like below.

f:id:skn_0410:20160701060001p:plain

We can change the format of respective field when we put multiple fields in same label.

Plus, in the formula, we don't put else statement which automatically set "null".

The "null" does not show up anywhere and does not show up as "blank" neither.

Then, even in the case that we put multiple fields in same field, the label is not duplicated, provided the condition restricts to show only one value in one of these fields.

 

This is the final VIZ.

f:id:skn_0410:20160701080218p:plain

 

OK, Let's play around and enjoy!!

public.tableau.com