5 Replies Latest reply on Aug 31, 2016 6:33 AM by Shinichiro Murakami

    Calculations

    RAVITEJA GUNDA

      Hi Folks,

       

      I am trying to calculate SUM(X) between two events

      For example, I have a table like the below

       

       

      Day          X               events

      1               30                    0

      2               30                    0

      3               30                    0

      4               30                    0

      5               30                    0

      6               30                    1

      7               30                    0

      8               30                    0

      9               30                    0

      10               30                    0

      11             30                    0

      12               30                    0

      13               30                    1

      14               30                    0

      15               30                    0

      16               30                    0

      17               30                    0

      18               30                    2

      19               30                    0

      20               30                    0

       

       

      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

       

      day      day between events          sum(X)

      6                     6                                         180

      13                    7                                         210

      18                      5                                      150

       

      Day field is date datatype.

       

       

      Thanks

        • 1. Re: Calculations
          Shinichiro Murakami

          Raviteja,

           

          Little bit complicated, but please see attached file.

           

           

          Create calculated field

           

           

          [Sum X]

          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

           

           

          [Count day]

          if index()=1 then count([Day])

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

          then count([Day])

          else count([Day])+previous_value(1)

          end

           

           

          [index]

          if attr([Events])<>0 then index() end

           

           

          Thanks,

          Shin

          • 2. Re: Calculations
            RAVITEJA GUNDA

            I tried doing the same but it does not work as expected.

             

             

            I have more field included in the data set

            P.S : Day is Date field. There might be missing dates aswell

             

             

            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)

             

            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

             

            6                    6                              180

            20                    14                            420 

            23                    3                                  30

            • 3. Re: Calculations
              Shinichiro Murakami

              I used table calculation and I cannot cheat Tableau's table calc with skipped days behind the scene in this case..

              Filtering A itself works, but again missing "X" (21,22) does not work.

               

              Thanks,

              Shin

              • 4. Re: Calculations
                Yuriy Fal

                Hi all,

                 

                RAVITEJA, you wrote:

                P.S : Day is Date field. There might be missing dates aswell

                This implies using Dates (or Day bins) instead of Day numbers,

                so one could activate domain padding on a view

                by setting Show Missing Values on Date (or Bin) field.

                 

                Please find the attached (Shin, I've borrowed your wb as well).

                 

                Yours,

                Yuri

                1 of 1 people found this helpful
                • 5. Re: Calculations
                  Shinichiro Murakami

                  Yuri,

                   

                  That's good idea.

                  Thank you for the following up.

                   

                  Shin