
1. Re: Row wise calculations
Andrew Watson Sep 13, 2016 12:17 PM (in response to Swati Venugopal)Col3: SUM([Cancels])/SUM([Bookings])
Col4: 1  [Col3]

2. Re: Row wise calculations
praveen p Sep 13, 2016 12:23 PM (in response to Swati Venugopal)Create a calculation as below
"cancel/bookings"
sum(Cancels)/sum(Booking)
one more calculation as below
1cancel/bookings

3. Re: Row wise calculations
Swati Venugopal Sep 13, 2016 12:26 PM (in response to Andrew Watson)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
Andrew Watson Sep 13, 2016 12:38 PM (in response to Swati Venugopal)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
Swati Venugopal Sep 13, 2016 12:45 PM (in response to Swati Venugopal)Do ignore the trial buckets column.
Thanks!

7. Re: Row wise calculations
Andrew Watson Sep 13, 2016 12:50 PM (in response to Swati Venugopal)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
Swati Venugopal Sep 13, 2016 1:20 PM (in response to Andrew Watson)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
Andrew Watson Sep 13, 2016 6:06 PM (in response to Swati Venugopal)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
Swati Venugopal Sep 13, 2016 10:17 PM (in response to Andrew Watson)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 nonaggregate 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
Andrew Watson Sep 14, 2016 4:56 AM (in response to Swati Venugopal)1) When I try the below formula, i get an error which says i cannot mix aggregate and nonaggregate 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
Swati Venugopal Sep 14, 2016 8:04 AM (in response to Andrew Watson)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!

Tableau1.twbx 177.3 KB


14. Re: Row wise calculations
Andrew Watson Sep 14, 2016 5:49 PM (in response to Swati Venugopal)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.

Tableau1 AW Edit.twbx 202.7 KB
