8 Replies Latest reply on Oct 11, 2018 7:20 AM by Simon Runc

    Grouping Formula

    Gary Cantigneaux

      Dear All,

       

      I am just starting with Tableau and i need to do for our company a KPI which is based on multiple column calculation such as below:

       

      Column1
      Column2Column 3KPI
      102030A*B-C = 10*20-30 = 170
      56031A*B*C = 5*60-31 = 269

       

      This can easily be done by creating a calculated field in the table but i also need to have this formula updated if i agregate all the results such as this:

       

      Column1
      Column2Column 3KPI
      158061A*B-C = 15*31-61 = 1139

       

      I hope my question is clear as i could not find any answers on the forum by doing the search. but maybe i did not use the right keywords.

       

      Thanks in advance for your help!

       

      Best regards,

       

      Gary

        • 1. Re: Grouping Formula
          Simon Runc

          hi Garry,

           

          So with regards your first part, you can do this as a "row level" calculation in Tableau. This works the same as if you'd done it in the data

           

          [Column1] + [Column 2] - [Column 3]

           

          To get an aggregated result, at what ever level is in you viz, you'd change it to

           

          SUM([Column1]) + SUM([Column 2]) - SUM([Column 3])

           

          so the values are SUMed before being combined.

           

          One note that if any of your columns are blank it will return null (1+1+null = null), so you might want to wrap a ZN around the fields

          zn([Column1])+ zn([Column 2]) - zn([Column 3])

          This just means nulls will be treated at zeros. Worth noting when using calculating involving multiple fields (unless you'd want a null, of course!)

          • 2. Re: Grouping Formula
            meenu choudhary

            Hello Gary,

             

            You can try below approach:

             

            KPI = sum([Column1])*sum([Column2]) -sum([Column 3])

             

             

            • 3. Re: Grouping Formula
              Gary Cantigneaux

              Hi Simon,

               

              Thanks a lot for your feedback and help, I did it in a tableau file, so it is easier. When you say we can do it in a “row level” calculation, you mean a calculated field in the view?

               

              I added the following formula: zn()+ zn() - zn() as proposed by you

               

              Now this is the starting table:

               

               

               

               

              And this is the final result:

               

               

               

              So it looks like it is working for the moment.

               

              I will try to get the real formula I need to use and get back to you.

               

              Thanks a lot

               

              Gary

              • 4. Re: Grouping Formula
                Simon Runc

                hi Gary,

                 

                Glad it helped. So by Row Level and Aggregate Calculation I mean that for some calculation types (row level) the calculation is performed row-by-row in the data, so the result of that calculation is irrelevant of what is in the VizLoD (the dimensions on the canvas), but for aggregate calculations the result is dependent on what is on the canvas. I refer to these as Off Canvas and On Canvas calculations. I wrote a Quora answer on this, that you might find useful (basically, IMHO, if you get this concept, you get Tableau!)

                 

                Answer - Quora 

                 

                hopefully the quora post helps see how

                 

                [Column1] + [Column 2] - [Column 3]

                 

                and

                 

                SUM([Column1]) + SUM([Column 2]) - SUM([Column 3])

                 

                are different.

                 

                In the first one the  [Column1] + [Column 2] - [Column 3] is done on every row and then you bring in the result as a SUM (say) at whatever level you have on the canvas

                 

                on the SUM([Column1]) + SUM([Column 2]) - SUM([Column 3]) the SUM is done at whatever level is on the canvas, and then the results of that are then used in the 1 + 2 - 3.

                 

                Hope that helps

                • 5. Re: Grouping Formula
                  Gary Cantigneaux

                  Hi Simon,

                   

                  I am sorry to say but it is not working, I have just tried with the following formula:

                   

                  Stock Quantity / Forecast 1: as you can see it is ok when I am at the lowest level (figure 1) but when I aggregate by removing one of the row field (in this case IPC) then the FMC calculation 2 is not recalculation the formula but just summing the data of all the IPC in the column (Figure2)

                   

                  Sorry but I am lost and I cannot find a solution.

                   

                  Thanks for your help

                   

                  Regards,

                   

                  Gary

                   

                   

                  Figure1

                   

                  Figure2:

                   

                   

                  I am also including the file in case this help you to help me ☺

                   

                  Thanks,

                   

                  Gary

                   

                  PS: I am reading at the moment your article.

                   

                  Regards,

                   

                  Gary

                  • 6. Re: Grouping Formula
                    Simon Runc

                    I'm out for the rest of the day, but try using this version

                     

                    zn(SUM([Column1])) + zn(SUM([Column 2])) - zn(SUM([Column 3]))

                     

                    which should work at any level.

                    • 7. Re: Grouping Formula
                      Gary Cantigneaux

                      Dear Simon,

                       

                      Thanks a lot, it looks like it is working. I hope now to be able to do this with the real data.

                       

                      Thanks again for your support

                       

                      Gary

                      • 8. Re: Grouping Formula
                        Simon Runc

                        Great news. If you read that Quora article is should make sense as to why one works and one doesn't