1 Reply Latest reply on Oct 15, 2018 10:27 PM by swaroop.gantela

    Continuous Hurricane Comparisons

    jeremy.haynes

      I have a database table with data similar to what is in Sheet1 of the attached excel file. The data is broken out between Hurricanes Irma from last year and Michael from this year and I want to chart out how many events were active at any given hour on Day 0 of the storm, Day 1, etc. I would like a data table and line chart similar to what is in Sheet4 of the attached excel file, showing how many events were active at each given hour. The problem I've run into is I can't make Start Day/Start Hour continuous and have them nested, if they are continuous, it makes 2 charts. To make one chart, I can make them discreet, but that breaks the line. I made a separate list of all days and hours as a csv, but it still won't do continuous. How do I do this?

        • 1. Re: Continuous Hurricane Comparisons
          swaroop.gantela

          Jeremy,

           

          I'm not sure if this quite got there (shape doesn't look quite right),

          but maybe it can give ideas.

           

          All the following scaffolding may not be necessary,

          as it sounds like you were almost there.

          Maybe the main part is just at the end with the datedding to #1899-12-31#.

           

          Scaffolding:

          I'm not sure if this will be feasible for your true datasource,
          but I needed to make a data scaffold which was just:
          ScaffoldDay | ScaffoldHour
          0 | 0
          0 | 1
          ...
          0 | 24
          1 | 0
          ...
          10 | 24

           

          Then I joined your datasheet to the scaffold
          with a calculated field of just 1.

           

          This makes excessive joins, so I needed to make a filter.
          First step was to convert the ScaffoldDay and ScaffoldHour into a proper DateTime using:
          MAKEDATETIME(
          MAKEDATE(YEAR([Event Start Time]),MONTH([Event Start Time]),DAY([Event Start Time])+[ScaffoldDay]),
          MAKETIME([ScaffoldHour],0,0))

           

          Then the filter becomes:
          [ScaffoldDateTime]>=DATETRUNC('hour',[Event Start Time])
          AND
          [ScaffoldDateTime]<=DATETRUNC('hour',[Event End Time])

           

          I made the continuous plot day using:

          DATEADD('hour',[ScaffoldHour],

          DATEADD('day',[PlotDay],#1899-12-31#))

           

          I custom formatted the date to "dd" and then moved the start point to get rid of the 31 on the axis.


          Please see workbook v10.3 and datasource attached in the Forum Thread.

           

          284879hurr.png