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]))
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:
Hope this helps a bit!
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.
events in a time window.twbx.zip 74.0 KB
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.
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?
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?
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.
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.
Hello, I am having a similar challenge but I cannot open the attachment on mac. Any chance of posting the calculation, please?