10 Replies Latest reply on May 9, 2017 12:45 PM by David Pavel

    Calculating total between two events

    RAVITEJA GUNDA

      Hi Guys,

       

      I have some data like

       

      Day          X                         Y    

      1               10                         0

      2               22                         0

      3               45                          0

      4               12                         0

      5               27                         0

      6               42                        1  (Event)

      7               22                         0

      8               23                         0

      9               4                          0

      10               15                        0

      11               2                          0

      12               444                      1(Event)   

      13               10                        0

      14               220                      0

      15               55                          1(Event)

       

      Both my X and Y are calculated fields

      I want to calculate SUM(X) between two events and also difference in days between two events

       

      Expected output

       

      Event_number          SUM(X)                                   Difference in Days

      1                        10+22+45+12+27+42                                           5

      2                         22+23+4+15+2+444                                           6

       

       

      Appreciate your help

        • 1. Re: Calculating total between two events
          Benjamin Greene

          How does this look?

          • 2. Re: Calculating total between two events
            David Pavel

            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

              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.

              • 4. Re: Calculating total between two events
                David Pavel

                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.

                capture.png

                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.

                1 of 1 people found this helpful
                • 5. Re: Calculating total between two events
                  Benjamin Greene

                  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 77-31-1=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.

                  1 of 1 people found this helpful
                  • 6. Re: Calculating total between two events
                    David Pavel

                    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

                      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, right-click 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.

                      1 of 1 people found this helpful
                      • 8. Re: Calculating total between two events
                        David Pavel

                        I think I got the outcome I was looking for.  Below is a draft viz.

                        • Blue bar: Current Pay Hour streak < Max Pay Hour streak
                        • Dark bar: Current Pay Hour streak = Longest Pay Hour streak
                        • Reference line (shaded below line) = Longest Pay Hour streak > Current Pay Hour streak

                        Thanks again! final view.png

                        • 9. Re: Calculating total between two events
                          Benjamin Greene

                          Nice, looks good! Hopefully Raviteja is OK with us commandeering his thread.

                          • 10. Re: Calculating total between two events
                            David Pavel

                            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?