0

I am creating a Tableau visualization for floor stock in our plant. We have a column for incoming date, quantity, and outgoing date. I am trying to create a visualization that sums the quantity but only while between the 2 columns.

So for example, if we have 9 parts in stock that arrived on 9/1 and is scheduled to ship out on 9/14, I would like this visualization to include these 9 parts in the sum only while it is in our stock between those 2 dates. Here is an example of some of the data I am working with.

4/20/2018 006 5/30/2018

4/20/2018 017 5/30/2018

4/20/2018 008 5/30/2018

6/29/2018 161 9/7/2018

jcrumbles
  • 3
  • 1
  • 3

3 Answers3

0

Create a new calculation:

if [ArrivalDate]>="2018-09-01" and [ArrivalDate]<"2018-09-15" and [Shipdate]<'2018-09-15"

then [MEASUREofStock] else 0 end

Lan Si
  • 82
  • 7
0

Here is a solution using UNIONs written before Tableau added support for Unions (so it required custom SQL) Volume of an Incident Queue at a Point in Time

For several years now, Tableau has supported Union directly, so now it is possible to get the same effect without writing custom SQL, but the concept is the same.

The main thing to understand is that you need a data row per event (per arrival or per departure) and a single date column, not two. That will let you calculate the net change in quantity per day, and you can then use a running total if you want to see the absolute quantity at the close of each day

Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49
  • 1
    This ended up fixing it. I reconfigured the table so there was one event per row, a positive quantity on arrival and a negative quantity on departure. Then I imported this into Tableau and used the Running total of stock by date. This outputs a table showing exactly how many parts we have in the building by day. Thank you for the assistance! – jcrumbles Sep 26 '18 at 13:07
  • glad I helped. Consider upvoting and accepting the answer. – Alex Blakemore Sep 26 '18 at 17:01
0

There is no simple way to display the total quantity between the two dates without changing the input table structure. If you want to show all dates and the "eligible" quantity in each day, you should

  1. Create a calendar table that has all dates start from 1990-01-01 to 2029-12-31. (You can limit the dates to be displayed in dashboard later by applying date filter, but here you want to be safe and include all dates that may exist in your stock table) Here is how to create the date table quickly.

  2. Left join the date table to stock table and calculate the eligible quantity in each day. 

SELECT
a.date,
SUM(CASE WHEN b.quantity IS NULL THEN 0 ELSE b.quantity END) AS quantity
FROM date a
LEFT JOIN 
stock b on a.date BETWEEN b.Incoming_Date AND b.Outgoing_Date
GROUP BY a.date
  1. Import the output table to Tableau, and simply add dates and quantity to the chart.
Larry Li
  • 133
  • 6