1 Reply Latest reply on Mar 5, 2018 7:43 AM by patrick.byrne.0

    Educational Brain Teaser (Answer for Time Duration Calc) : Graph of "Time" replicates Excel (Easy?)

    Shinichiro Murakami

      Question :

      Educational Brain Teaser : Graph of "Time" replicates Excel (Easy?)

       

      Answer Part I =Data Prep

        Educational Brain Teaser(Answer Part I) : Graph of "Time" replicates Excel (Easy?)

      ***************************************************************************************************

      I cleansed the initial data at part I, then I can focus on the main topic 

      What was the problem?

       

      Excel is smarter than Tableau!!! especially for "Time" calculation.

       

      Let's start from re-capping the excel functions.

      Using exact same value, only with changing the "format" you can show such a variety of format.

      Beautiful!

       

      Couple of difference between Excel and Tableau.

       

      1. Excel can show day "0" as "1900/1/0 " !!!!

      2. with adding "[]" around time unit, Excel can show more than 24 hours, more than 60 minutes, or more than 60 seconds.

       

      While, Tableau

      1. The oldest Date is 1900/11

      2. Tableau cannot show show more than 24 hours, 60 minutes, or 60 seconds.

       

      In other words,

      Tableau can show "current time" but cannot show "time duration" bu hour, minute, second.

       

      However, users want that, plus Tableau equips "Time duration Calculation".

       

      "datediff" is that formula, but you need pay attention to use this formula, that is pint -1 in this post.

       

       

      Point -1 Calculate Time duration with "datediff"

       

      Above table shows each time unit's calculation results with datediff.

      It calculates each units' value with cutting off the value one layer below.

       

      It brings back more than 24 hours, 60 minutes, or 60 seconds, but

      the "hour" difference between 0:59 ~ 1:01 becomes "1".

      It calculates the difference between 0 and 1.

       

      Then, how to get Excel's equivalent value as time display?

      Use the lowest/smallest level of unit, which is "second" in this case.

       

      1 minute = 60 seconds  /   1 hour = 3600 seconds, so, for example

       

       

      Use above "second" as base number,

       

       

       

       

      And we don't need more than 60 of "minute" and "second"

       

       

       

      Those brings below results.

       

       

      and when we combine these fields to show time formatted value,

       

       

      This technique to show "00" two digits always is useful at other case as well.

      str([Diff Sec H])+":"+right(str(100+[Diff SecMod M]),2)+":"+right(str(100+[Diff SecMod S]),2)

       

       

      ********************************************************************************************************************************

       

      Any other simpler way?

       

      maybe..

      use datepart, instead.  little bit less operations, and easier to show what we are doing on the formula.

       

      *

       

      *

       

      *

       

       

      One consideration is needed to align hours to more than 24 hours.

      As mentioned at beginning, Tableau does not have "Jan 0" as date, when we convert "day" to "hours", the correct calculation should be

       

      (day-1 )x24 + hours

       

      At next post, I will explain about how to draw a chart finally.

       

       

      Regards,

      Shin