1 Reply Latest reply on Sep 19, 2016 3:18 PM by Patrick A Van Der Hyde

    Calculation - SUM between events

    RAVITEJA GUNDA

      Hi

       

      I have some data like below for each product. . where day field is a date data type.

       

      I am trying to calculate days between events and sum of X between events. and I would like use type field as a filter. When a select particular type it needs to recalculate the days between events of the selected and sum of X between events of selected type. Sample output requirement is given below. I am trying to build a line chart with date field on columns and product id, daysbetween, sum(X) between on rows

       

       

       

      Day          X               events               Type

      1               30                    0                    NULL

      2               30                    0

      3               30                    0

      4               30                    0

      5               30                    0

      6               30                    1                    A

      7               30                    0

      8               30                    0

      9               30                    0

      10               30                    0

      11             30                    0

      12               30                    0

      13               30                    1                    B

      14               30                    0

      15               30                    0

      16               30                    0

      17               30                    0

      18               30                    2                    C

      19               30                    0

      20               30                    1                    A

      23               30                    1                    A

       

       

      i want to create a graph days between two events and SUM(X) between two events. for the first even I need to calculate SUM(X) until the first event.

       

       

       

      The result would be like (Across all types). This output is when I select all types from the filter

       

      day      day between events          sum(X)

      6                     6                                         180

      13                    7                                         210

      18                      5                                      150

      20                        2                                       60

      23                         3                                        30

       

       

      The result when computed for one Type A this is when I select Type A from the filter.

       

      6                    6                              180

      20                    14                            420

      23                    3                                  30

       

       

      Currently I am doing:

       

      for days between events:  

      IFNULL((datediff('day',

                          lookup(min([Day of ReadDate]), -1),

                          min([Day of ReadDate]))),0)

       

      And sum(X) between events

       

      if index()=1 then sum([X])

      elseif lookup(sum([Events]),-1)<>0 and lookup(sum([Events]),0)=0

      then sum([X])

      else (SUM([X]))+previous_value(1)

      end

       

       

      This works when i select all event types . but when select particular event type. The SUM(X) doesnt work as expected.

       

      Thanks for your help

        • 1. Re: Calculation - SUM between events
          Patrick A Van Der Hyde

          Hello RAVITEJA GUNDA

           

          I worked through this a bit and I'm having a challenge with the source data in the example.  Would you have a copy of the Tableau Workbook (.twbx) with the data for this issue?  Also, please let us know what version of Tableau you are using and if you need to stay with the same version in the reply or if it is okay to update the file to version 10 (latest version) as the many of us in the community utilize the most recent version of Tableau for the majority of our assists. 

           

          Thank you,

           

          Patrick