tableau-apilevel-of-detail

Count rows that meet certain requirements and then subtract that number from another count


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.


Solution

  • 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

    enter image description here

    Results

    enter image description here