1 Reply Latest reply on Jul 17, 2013 5:24 AM by Robin Kennedy

    Binning on dates and subsequent analysis (with screenshots and twbx)

    eraufars

      I have a set of events that occur on certain dates and times. (See workbook).

       

      To analyse them, I want to group the events by 10 minute intervals.

       

      I found these posts describing how to create such intervals and it works fine.

      Datetime Bins

       

      Using Custom Date Levels | Tableau Software

      Then, for the analysis, I want to see for each interval how many events occured. This works fine too (see workbook).

       

      Following that, I want to calculate the fraction of intervals that have > X events occuring.

       

      I was able to do this as well and put them in a table, but it is getting ugly.

       

      The result is there, (see the table), but what I really want to do is just display two numbers in a table:

      - the average number of events per interval (total events / number of intervals)

      - the fraction of intervals that have too many events (in the workbook, the threshold is set to 3).

       

      All the other numbers and intermediate steps are irrelevant to the end user.

      The calculations that allowed me to do this were made using WINDOW_AVG, WINDOW_SUM and WINDOW_COUNT functions on the table that includes the number of events per time interval.

      It seems that only in this table can I get my final numbers, I cannot 'just calculate them' and display them on their own.

       

      I did the same thing with SQL and it was quite easy to get the numbers I need, but I want to do it in Tableau so that I can also do other things with the complete data set.

       

       

      A final question which is less important: Why can't I get a grand total sum of the "Too many" column (sheet 3) ?

      It does it right for number of events and for the average column, but it does not sum up the others.

       

      Workbook is attached.