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

    Row wise calculations

    Swati Venugopal

      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
          Andrew Watson

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

           

          Col4: 1 - [Col3]

          • 2. Re: Row wise calculations
            praveen p

            Create a calculation as below

             

            "cancel/bookings"

             

            sum(Cancels)/sum(Booking)

             

            one more calculation as below

             

            1-cancel/bookings

            • 3. Re: Row wise calculations
              Swati Venugopal

              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

                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))

                • 5. Re: Row wise calculations
                  Swati Venugopal

                  Doesn't work.

                   

                  Result:

                  • 6. Re: Row wise calculations
                    Swati Venugopal

                    Do ignore the trial buckets column.

                    Thanks!

                    • 7. Re: Row wise calculations
                      Andrew Watson

                      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

                        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

                          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

                            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.

                            • 11. Re: Row wise calculations
                              Swati Venugopal

                               

                              P.S apologies for pasting images, my actual typed in answer goes into a 'currently being moderated' status.

                               

                              - Swati

                              • 12. Re: Row wise calculations
                                Andrew Watson

                                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
                                  Swati Venugopal

                                  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
                                    Andrew Watson

                                    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.