4 Replies Latest reply on Oct 26, 2018 9:39 AM by Steve Martin

    Calculation issue

    vij002

      Hi All,

      I am looking for help in below calculation

       

      Based on below table , I am looking to total based on IDs column but if st_dt and end_dt  for an ID is same in rows then take max and if different for same id then Add

       

       

           

      DateIDsSt_DtEnd_DtAmount
      26/10/20181126/10/201826/10/20181
      27/10/20181127/10/201827/10/20181
      8/01/20192228/01/201910/01/20193
      9/01/20192228/01/201910/01/20193
      10/01/20192228/01/201910/01/20193

      thus it shud be below result

      ID       Total Amount

      11          2     ( since for ID 11 st _dt and end _Dt in both rows were different thus we added Total column)

      222          3  (since st_dt and End_dt for ID 222 is same  thus we took MAX)

       

      Thanks for the Help

        • 1. Re: Calculation issue
          Steve Martin

          Hi vij,

           

          Table calcs can come to the rescue here. Paste this calc to a new calculated field and add to your measures setting the scope to your IDs field:

           

          If Attr([St Dt]) = Attr([End Dt]) Then Sum([Amount]) + Previous_Value(Sum(Amount))

          Else Window_Max(Sum(Amount))

          End

           

          By setting the scope to the IDs field, you will have set the pane-up so that Tableau will restart the calculation for each new id.

           

          For clarity, the pane is the scope or grain which you have applied in this case, ID so each new ID is treated as a new pane for which the calculation will reset.

           

          I expect there may be more to your requirement than you have provided that looks at what happens if some dates are aligned and some aren't but for this solution, what Tableau is doing is:

           

          Summing the Amount field and then adding the previously summed value to the latest summed value in the pane. Now, Tableau will do this to every record in the set until the date differs at which point, Tableau will check the whole pane for the highest value and then apply this to every record in the window.

           

           

          Steve

          • 2. Re: Calculation issue
            vij002

            Hi Stevie,

            Thanks for replying.

            I think you got it wrong( Attr() = Attr() .)

            what i meant was when st_dt n End_dt of 2 rows are same(so st_dt of row 1 =

            st_dt of row 2 and end _dt of row1 = end _dt of row 2)  then amount  should

            be max of both 2 rows else  amount 1 + amount 2

            Thanks

             

            On Wed, 24 Oct. 2018, 02:39 Steve Martin <tableaucommunity@tableau.com

            • 3. Re: Calculation issue
              Yuriy Fal

              Hi vij002,

               

              You may want to try an LOD expression like this:

              SUM( {INCLUDE [IDs], [St_Dt], [End_Dt] : MAX( [Amount] ) } )

               

              Yours,

              Yuri

              • 4. Re: Calculation issue
                Steve Martin

                Actually Vij, I did not get it wrong, the calculation I provided was the exact one I used to produce your required output.

                 

                Did you try it first before replying?

                 

                Steve