8 Replies Latest reply on Apr 25, 2017 4:35 AM by George Economides

    Calculate # of events in a time window

    Sean Mullane

      For each Case in this data set, I'm trying to calculate how many cases were opened within 5 minutes before the case was opened. E.g. if there was a case with ID# 123456 that was opened at 9:45 AM Jan 1, I want to know how many other cases were opened between 9:40 AM and 9:45 AM Jan 1 and assign that number as a weight to case 123456. The overall goal is to calculate a sum of weighted cases for each Case Owner.

       

      Assumptions:

      Cases come in irregularly (some hours may have many, some hours may have few or none).

      Case ID #s are unique.

      Rows are sorted by date/time opened.

      No more than 10 cases are created within any 5 minute period.

       

      My thought here was to create a table calculation that would use a WINDOW_SUM with an IF statement inside to determine whether each of the 10 previous cases were created within 5 minutes before the current case. I have no idea how to set up the partitions to make this actually work though. Any ideas? Sample data is attached.

        • 1. Re: Calculate # of events in a time window
          Tracy Rodgers

          Hi Sean,

           

          One option is to do something similar to this by creating a couple of calculations. This first calculation will look at the following rows to determine whether they are within the specified 5 minutes:

           

          if datediff('minute', max([Date/Time Opened]), lookup(max([Date/Time Opened]), 3))<=5 then attr(str([Case Number]))

          elseif datediff('minute', max([Date/Time Opened]), lookup(max([Date/Time Opened]), 2))<=5 then attr(str([Case Number]))

          elseif datediff('minute', max([Date/Time Opened]), lookup(max([Date/Time Opened]), 1))<=5 then attr(str([Case Number]))

          end

           

          From there, you can do a couple of other calculations to get the count:

           

          if ifnull([Calculation2], "0")="0" then 0 else (count([Case Number])) end

           

          Then, finish it off with:

           

          window_sum([Calculation3])

           

          Hope this helps a bit!

           

          -Tracy

          • 2. Re: Calculate # of events in a time window
            Joe Mako

            Attached is a route that uses:

            - custom SQL to duplicate the data, enabling the desired domain padding

            - domain padding to generate the 5 previous dates from each case's open date

            - data blend with normal data source as secondary, with relationship set by renaming fields

            - table calc to sum the blended values, returning a single value for each Case (partition), and advanced compute using to prevent domain completion

            - all together produces a flexible result

            - can add additional detail fields as an attribute, ATTR()

             

            I walked through this situation with Shawn Wallwork , let us know if you have any questions, there are quite a few advanced concepts used at once here.

            1 of 1 people found this helpful
            • 3. Re: Calculate # of events in a time window
              Richard Leeke

              Very clever, Joe. It took me quite a while to get my head around what is happening here, but it it does give a very good illustration of the sorts of things that can be achieved by anyone who has their head around data densification like you do (which currently means just you, I think ).

               

              An alternative, and to my mind much simpler approach (though less fun) is just to do the whole thing with RAWSQL, as per the attached.

               

              Note that my approach gives a slightly different answer to yours because I interpreted the 5 minutes before each event as being inclusive of the minute when that event was created. So for an event created at 12:10:00 you counted the events from 12:05:00 to 12:09:00 and I counted 12:06:00 to 12:10:00. Actually I think neither of those approaches is quite right for the case where two events are created in the same minute (like 11499280 and 11499281). You don't count those at all and I count each as having happened in the 5 minutes before the other.

              • 4. Re: Calculate # of events in a time window
                Sean Mullane

                Hi Tracy,

                 

                Thanks for the help. I'm not sure how your calculation gets a correct count of case numbers. Since you're using an elseif statement, wouldn't the count be 0 or 1? Or is there something I'm missing?

                • 5. Re: Calculate # of events in a time window
                  Sean Mullane

                  Thanks Joe! No kidding about the advanced concepts, this is quite a tricky piece of work. Still trying to wrap my mind around it. I can't tell whether this approach allows me to go one step further and sum over the rows at the end. The multiple data sources seem to limit any aggregation using Total(). Would it be possible to turn on grand total and somehow hide the rows themselves, or is there a way to extend the table calculation that I'm missing?

                  • 6. Re: Calculate # of events in a time window
                    Joe Mako

                    You can nest it in another WINDOW_SUM() as in the attached.

                     

                    see http://drawingwithnumbers.artisart.org/customizing-grand-totals-part-3/ and the 3 parts for more details on grand total control in Tableau.

                     

                    I would really recommend Richard's route, and you will also want to take a close look at your business logic for counting previous cases, it is ambiguous.

                    • 7. Re: Calculate # of events in a time window
                      Sean Mullane

                      Thanks to you too, Richard! I agree with Joe that your solution is probably the one I want to use here, since with only one data source it will probably be more extensible. I haven't used the rawsql functions myself yet, but clearly I need to start.

                       

                      Good catch on the inclusive/exclusive issue as well. I think leaving it inclusive is actually ok for my needs, as double-counting will be alright in this case.

                      • 8. Re: Calculate # of events in a time window
                        George Economides

                        Hello, I am having a similar challenge but I cannot open the attachment on mac. Any chance of posting the calculation, please?

                         

                        Thank you

                        George