14 Replies Latest reply on Sep 14, 2016 5:49 PM by Andrew Watson

# Row wise calculations

Hi, I want to do row wise calculations on Tableau, and I am not able to figure out how to do so.

I want a way to calculate column 3 (cancels/bookings) and column 4 (1 - (cancels/bookings)).

can someone help me out on this please?

Data: Thanks!!

• ###### 1. Re: Row wise calculations

Col3: SUM([Cancels])/SUM([Bookings])

Col4: 1 - [Col3]

• ###### 2. Re: Row wise calculations

Create a calculation as below

"cancel/bookings"

sum(Cancels)/sum(Booking)

one more calculation as below

1-cancel/bookings

• ###### 3. Re: Row wise calculations

Hi Andrew,

Cancels OR column 1: SUM(IF [CF_Status] = 'Cancelled'  THEN 1 ELSE 0 END)

Bookings OR column 2 is: PREVIOUS_VALUE(max([CF_PolCnt_PrevVal])) - Cancels

And, CF_PolCnt_PrevVal = {[CF_CountD_PolicyCounts]}

So i cannot use sum() since it does not take aggregates as it's expression.

Do you have other suggestions?

• ###### 4. Re: Row wise calculations

What happens if you just do [Cancels]/[Bookings]?

Or: SUM(IF [CF_Status] = 'Cancelled'  THEN 1 ELSE 0 END)/(PREVIOUS_VALUE(max([CF_PolCnt_PrevVal])) - SUM(IF [CF_Status] = 'Cancelled'  THEN 1 ELSE 0 END))

• ###### 6. Re: Row wise calculations

Do ignore the trial buckets column.

Thanks!

• ###### 7. Re: Row wise calculations

Can you check your brackets in the formula? The Denominator calculation needs to happen first so should be in brackets.

• ###### 8. Re: Row wise calculations

Hey Andrew,

I double checked the brackets.

They seem fine; in fact the solution which you have pasted has the correct brackets present.

• ###### 9. Re: Row wise calculations

Please check the PREVIOUS_VALUE calculation is calculating using the appropriate 'Compute Using', it should be the same in your Booking and Cancels/Bookings.

If you're still having no joy this may work: SUM(IF [CF_Status] = 'Cancelled'  THEN 1 ELSE 0 END)/(ZN(LOOKUP(max([CF_PolCnt_PrevVal]),-1)) - SUM(IF [CF_Status] = 'Cancelled'  THEN 1 ELSE 0 END))

This is removing the PREVIOUS_VALUE and replacing with LOOKUP. This may help explain why: PREVIOUS_VALUE vs LOOKUP([expr],-1)

• ###### 10. Re: Row wise calculations

Hi Andrew,

Thanks so very much for the help, I have been stuck here since a couple of days without any progress, so its much appreciated!

But unfortunately this did not work. Screenshot below (Col 5: CF_Div) I basically want to achieve Col 4 / Col 3 (CF_PrevValCurve / CF_PolCnt_PrevVal), which will give me how the bookings are dropping over  time.

I tried a couple of things and had some questions around it, and was hoping you could help:

1) When I try the below formula, i get an error which says i cannot mix aggregate and non-aggregate functions, is there a work around for this?

PREVIOUS_VALUE(max({[CF_CountD_PolCnt]})) - (SUM(IF [CF_Status] = 'Cancelled'  THEN 1 ELSE 0 END))

/

{[CF_CountD_PolCnt]}

2) And, when i tried to do:

[CF_PrevValCurve]

/

MAX([CF_PolCnt_PrevVal])

I get the very first value correct, but the rest of the values are incorrect.

I have figured out the manner in which Tableau calculates for this in the background:

For the second row, it does, 5300 + 1142 / 6992; Instead it should just be 5300 / 6992.

For the third row, it does, 4801 + 550 + 1442 / 6992; Instead it should just be 4801 / 6992.

Screenshot below: And so on, again i am at a loss of a way to get around this.

• ###### 12. Re: Row wise calculations

1) When I try the below formula, i get an error which says i cannot mix aggregate and non-aggregate functions, is there a work around for this?

PREVIOUS_VALUE(max({[CF_CountD_PolCnt]})) - (SUM(IF [CF_Status] = 'Cancelled'  THEN 1 ELSE 0 END))

/

{[CF_CountD_PolCnt]}

Try

PREVIOUS_VALUE(max({[CF_CountD_PolCnt]})) - (SUM(IF [CF_Status] = 'Cancelled'  THEN 1 ELSE 0 END))

/

MAX({[CF_CountD_PolCnt]})

2) And, when i tried to do:

[CF_PrevValCurve]

/

MAX([CF_PolCnt_PrevVal])

Sounds like your CF_PrevValCurve is doing a running_sum. Personally I would change it to LOOKUP.

You'll need to attach a twbx file as cases like this as very hard to troubleshoot without seeing exactly what is happening.

• ###### 13. Re: Row wise calculations

Hi Andrew,

I did try MAX({[CF_CountD_PolCnt]}) prior to posting the question here; doesn't work.

I have attached the packaged workbook here, sheet 32 is where I have pulled in the columns to check for the values.

Do let know if you need any additional information.

Thanks a bunch!

• ###### 14. Re: Row wise calculations

I've now gone through this to try and understand what is happening. I've redone some of your calculations, for example you were sometimes doing a distinct count on policy number and other times counting records (by assigning a 1 to relevant records and then summing) - which have very slightly different results. For consistency I have used the distinct count on policy number all of the way through, feel free to change to record counts if you prefer.

I have abandoned the PREVIOUS_VALUE and replaced with formulae with which I'm more comfortable, such as a RUNNING_SUM.

Hopefully you can work out what is happening, I've laid it out in a table so shouldn't be too complex to work out.

If I have your % calculations wrong you should be able to use the fields created to correct the calc.