Let's say I have data like this:
Type | OrderNumber | Priority | DeliveryMethod |
---|---|---|---|
Boxes | 1 | High | UPS |
Misc | 1 | High | UPS |
Boxes | 2 | Standard | InstaBox |
Boxes | 3 | Standard | UPS |
Boxes | 3 | Standard | UPS |
Boxes | 3 | Standard | UPS |
Boxes | 4 | Standard | Instabox |
Boxes | 5 | Standard | Instabox |
Boxes | 5 | Standard | Instabox |
Boxes | 6 | Standard | UPS |
Boxes | 7 | Standard | UPS |
And I want to count all the so called "Private Orders". They are boxes with an unique ordernumber and a Standard priority and UPS delivery. (There's 2 in this example, 6 and 7) Then I want to count all the boxes with Standard priority and subtract all the Private orders. (There's 9 boxes with Standard priority, minus the 2 Private orders = 7.)
Is that possible in Tableau? I want to display the number 7 in a textbox.
For Private orders
use this field
IF
{FIXED [Type], [Ordernumber] : COUNT([Ordernumber])} = 1
AND [Type] = 'Boxes' And [Priority] = 'Standard' And [DeliveryMethod] = 'UPS'
THEN 1 ELSE 0 END
For all boxes use this
Sum(
If [Type] = 'Boxes' And [Priority] = 'Standard' then 1 else 0 end
)
SO FOR FINAL OUTPUT YOU MAY USE THIS DIRECTLY say calculation2
Sum(
If [Type] = 'BOXES' And [Priority] = 'Standard' then 1 else 0 END
) -
Sum(IF
{FIXED [Type], [OrderNumber] : COUNT([OrderNumber])} = 1
AND [Type] = 'BOXES' And [Priority] = 'Standard' And [DeliveryMethod] = 'UPS'
THEN 1 ELSE 0 END)
For matching pattern change the above field to say calculation3
Sum(
If [Type] = 'BOXES' And [Priority] = 'Standard' then 1 else 0 END
) -
Sum(IF
{FIXED [Type], [OrderNumber] : COUNT([OrderNumber])} = 1
AND [Type] = 'BOXES' And [Priority] = 'Standard' And REGEXP_MATCH([DeliveryMethod], "UPS")
THEN 1 ELSE 0 END)
the data used
Results