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

# Calculation issue

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

 Date IDs St_Dt End_Dt Amount 26/10/2018 11 26/10/2018 26/10/2018 1 27/10/2018 11 27/10/2018 27/10/2018 1 8/01/2019 222 8/01/2019 10/01/2019 3 9/01/2019 222 8/01/2019 10/01/2019 3 10/01/2019 222 8/01/2019 10/01/2019 3

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

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

Hi Stevie,

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

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

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