I have some sales data that I want to figure out total sales by product. The issue arises because the Point of Sale system handles voids as a separate row. See the sample below.
Transaction 101 should not be included in the sales of Coke because the void number sold is really a negative number conceptually. Is there some way to handle this in either Tableau or Tableau Prep? If I simply filter out all rows with “Void” in the Product Description, my total number sold will be too high because of the voids.
One thought I had was to perform a calculation. Create a new column for “Number Sold Actual”. If product description contains the word “VOID”, then multiply the “Number Sold” column by negative one and place in “Number Sold Actual” otherwise multiply “Number Sold” by Plus one and put in Number Sold Actual. This should work, I am just not sure of the exact syntax. Any help is appreciated.
Numbers Actual
IF CONTAINS(Prod Desc, "Void") THEN (-1*Number Sold) ELSE Number Sold END