2 Replies Latest reply on Apr 21, 2017 2:57 AM by Steven Mullin

# Lookups, Fixed Calcs, Offsets (Automating Excel Process)

Hey guys,

I'm having a very hard time getting my latest project to work properly.  A colleague spends a lot of time extracting data from a database and working on it in Excel to create visualisations.  The biggest challenge is to do with the way that the data is stored in the table, which is as good as it will get.  So hopefully I can get Tableau to pick up the slack.

Here is an image of the data as it is pulled into Excel.  The Tableau datasheet is pretty much the same.

The colours have been added later.  This sheet is showing batch changeovers.  We currently have to look at the start and finish times to determine where the changeover starts and ends and then add up the durations within the coloured sections.  The Lot numbers confuse the issue, but there is a clear pattern.

The desired visualisation would have 'StartTime' in Columns as well as the 'Lot'.  Duration would be in Rows and would be the sum of the durations for each time within the changeover.  But the required Lot no is the one at the end of the changeover. The lot detailed at the start time is actually the previous lot.

I have previously attempted this by offsetting the lot numbers but then I have issues putting that into a Fixed calc to get the durations.  I hope I have managed to explain my requirement clearly.

I have attached a workbook with captions on the sheets which may help to explain the problem.  Version 10.1.

• ###### 1. Re: Lookups, Fixed Calcs, Offsets (Automating Excel Process)

Steven,

I'm not sure I fully understand what you are trying to achieve, but I have come up with a solution that might work for you.  Give this a try:

Create a calculated field that picks up the start date-time from the previous record:

Create a calculated field that picks up the finish date-time from the previous record:

Create a calculated field to determine if the start date-time of this field is the same as the finish date-time of the previous field:

Create a calculated field to calculate the total duration:

Now, on the viz, place the [InitialStart], [Start], and [Lot] on the Columns Shelf, and place [TotalDuration] on the Rows Shelf:

For the [InitalStart], and [TotalDuration] fields, make sure to select Compute Using "Table (across)":

Place the [CurrentStartEqualInterimFinish?] on the Filters shelf, and make sure it is also Compute Using "Table (across)", and set the filter to just show the "True" values.  And last, for the [Start] field on your columns shelf, be sure to un-mark the "Show Header" option, so that it is hidden.  Then your final viz should look like this:

Kaz.

• ###### 2. Re: Lookups, Fixed Calcs, Offsets (Automating Excel Process)

Hi Kaz,

Sorry for the delay in replying.  I've been working on what you've given me.  You absolutely have the right idea and your modified workbook is brilliant.

Unfortunately the data source isn't as tidy as it ought to be.  If you look at sheet 5 you'll notice that Lot 4159465 is repeated, but 4159662 only has a value of 388 seconds.  This is due to the 4159662 changeover taking place over 4 rows in the dataset, as you can see in my original image.  Is it possible to have a Lookup that can detect these odd sequences?

I also have a section in the original image that is highlighted red.  This is where the finish time doesn't exactly match the next start time.  Unfortunately this also occurs quite often.

I'm going to carry on working on this using your methods as a jumping off point.  You've taken me further than I was able to get yesterday.  Hopefully I can figure it out, but I would appreciate any extra help.  I don't like to think something isn't possible...