
1. Re: Calculating total between two events
Benjamin Greene Aug 11, 2016 1:37 PM (in response to RAVITEJA GUNDA)How does this look?

Book1.twbx 19.1 KB


2. Re: Calculating total between two events
David Pavel Aug 22, 2016 8:29 AM (in response to Benjamin Greene)Benjamin, I'm trying to apply your solution to a similar situation.
I'm struggling with how to apply this as my DAY column is actually a Date field. The days between events can range for just a few days to 400+ days.
Any thoughts on how to get a DATEDIFF into this solution?

3. Re: Calculating total between two events
Benjamin Greene Aug 23, 2016 6:52 AM (in response to David Pavel)I went ahead and mocked up some random data to expand the dataset a bit and make that Day field into a date. Let me know if the changes to the Difference in Days field work for you.

Book1 (5).twbx 23.9 KB


4. Re: Calculating total between two events
David Pavel Aug 25, 2016 6:25 AM (in response to Benjamin Greene)Thanks for your reply.
I'm so close, but the SUM(X) calculation is not working as expected.
In your example, every date is present, while in my dataset there are gaps in the dates. In my data, X = worked hours and there are days where people didn't work.
I'm not sure is this is the cause as I don't understand what SUM(X) is doing. I have attached a sample workbook and what the desired outcome should be.
Note, I would really like to show the Today, or the last day in the dataset, as an Event to show the current Work Hours and Days since the last 'real' Event.
Any help is most appreciated.

Event Difference.twbx 40.9 KB


5. Re: Calculating total between two events
Benjamin Greene Aug 26, 2016 6:41 AM (in response to David Pavel)Good catch, David. If the number of days since the last event is greater than the number of rows since the last event (i.e. if there are some dates not in the data set), then the SUM([X]) calculation will not be correct. To remedy this, I made a Row Difference calculated field and used that one in the SUM([X]) calculation instead. For a little more clarity on how this calculation works, it is summing all the X values in a specified range in the table. This range changes depending on what rows the current event and previous event occur in. For instance, if the current event occurs in row 77 and the previous event occurred in row 31, then Tableau looks backwards 77311=45 rows to row 32 and sums all the X values from there to row 77.
Your other request (about forcing the first and last days in the data to be events and flagging them accordingly) is also rather easy. I just made a Y (Adj) field to do this and created a Comment calculated field too. I also changed all the references to [Y] in the other calculated fields so that they instead reference Y (Adj).
I have attached a workbook with the solution in Sheet 1, and a view of all the underlying data in Sheet 2. It looks like the numbers in your desired outcome are still different from what is coming up. It looks like your Sum Pay Hours is omitting the hours worked on the exact day the event occurs. Is this intentional? If so, and you do not want to include these hours in the sum, just change that last 0 in the SUM([X]) calculation to a 1.

Event Difference.twbx 65.7 KB


6. Re: Calculating total between two events
David Pavel Aug 26, 2016 9:26 AM (in response to Benjamin Greene)Thank you for the explanation  This is great! I just need to use some of these features (WINDOW, INDEX) more as they are quite powerful.
This view is great as a bar graph to visualize the Pay Hours between events, which leads me to one last question. Showing all events is nice, but what if I only wanted one bar to compare the Current Pay Hours since the last event vs the Event with the most Pay Hour? In my data...
 Bar = Current Period ( MAX Date through the Last Event ) = 1,097,068
 Reference Line = Pay Hours in the Maximum period between events = 1,714,808
How would I isolate just those two instances?

7. Re: Calculating total between two events
Benjamin Greene Aug 26, 2016 11:02 AM (in response to David Pavel)Glad to help! And yes, this is very doable. You'll want to drag Date to columns (set to Exact Date and Discrete) and SUM(X) to rows then filter on Event Number so that only the largest number is selected. Make sure your mark type is set to Bar (and mark labels are enabled) and then make a new calculated field (I called it Max Pay Hours) as WINDOW_MAX([SUM(X)]) and drag this to detail. Next, rightclick the axis and select "Add Reference Line" and set the value to Max Pay Hours and Average. I prefer making a custom label as "Max Pay Hours: <Value>" and then clicking OK. Sheet 3 has this solution.

Event Difference.twbx 75.3 KB


8. Re: Calculating total between two events
David Pavel Aug 26, 2016 12:28 PM (in response to Benjamin Greene) 
9. Re: Calculating total between two events
Benjamin Greene Aug 26, 2016 12:42 PM (in response to David Pavel)Nice, looks good! Hopefully Raviteja is OK with us commandeering his thread.

10. Re: Calculating total between two events
David Pavel May 9, 2017 12:45 PM (in response to Benjamin Greene)Benjamin, many months ago you helped me with this challenge and all is good until now the client wants the dates sorted descending  which breaks all the Index calculations.
I have messed around way too long trying to get things working with no results.
Using the latest attached file, what changes are needed to the SUM(X), Row Difference and Days Difference calcs. when date is descending?