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.
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 0and
{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.
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.
Double click on the red color of the category of "1- purchased both".
Then pick darker color.
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
and open detail setting window.
Remove break line, select only <HighLight>, and change the format like below.
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.
OK, Let's play around and enjoy!!