4 Replies Latest reply on Jun 28, 2013 5:57 AM by Vincenzo Manzoni

    How to compute a row from two?

    Vincenzo Manzoni

      Dear all,

       

      I have a table such this:

       

      codesignaloperationvalue
      335145PQF_J6_SF_DPMean39,90
      335145PQF_J6_SF_DPMax183,63
      335146PQF_J1_SF_DPMax2180,82
      335146PQF_J1_SF_DPMean2013,08

       

      I would like to add the two new rows listed hereafter:

       

      335145PQF_J6_SF_DPPerc183.63 / 39.90 * 100
      335145PQF_J6_SF_DPPerc2180.82 / 2013.08 * 100

       

      Is there any way to do it in Tableau?

       

      Thanks all!

       

      - Vincenzo

        • 1. Re: How to compute a row from two?
          Joshua Milligan

          Vincenzo,

           

          Sure!  It can be done.  I've attached a workbook to show one way of doing it.  The workbook contains inline documentation, but the basic idea is to create a calculated field, which is a table calc, to use values from each of the pair (Mean row and Max row).

           

          If you aren't too familiar with table calculations, you might want to do some browsing through the knowledge base.  The main thing is to get the table calcs setup with the right addressing / partitioning.  In this case the calculation and setup looks like this (the workbook contains more detailed explanation):

           

          Table Calc.png

           

          Regards,

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Re: How to compute a row from two?
            Vincenzo Manzoni

            Hi Joshua,

             

            Thanks for your answer!

             

            I tried another way:


            1. I created two computed fields as follows:

            • Max_comp: IF [operation] = 'Max' THEN [value] END
            • Mean_comp: IF [operation] = 'Mean' THEN [value] END

            2. I created a third computed field, Max / Mean, as [Max_comp]/[Mean_comp].

             

            While I can plot histograms of Max_comp and Mean_comp, I cannot plot the histograms of Max / Mean.

             

            I attached the workbook. Can you please have a look at it?

             

            Thanks!

             

            Best,

             

            - Vincenzo

            • 3. Re: Re: How to compute a row from two?
              Joshua Milligan

              Vincenzo,

               

              The reason that is not working is that your third calculation is a row-level calculation.  That means that it is calculated for each row of data from the source.  But if you look at the source data, either Max or Mean is NULL in each row, so Max / Mean will always be NULL (see image below).

               

              Do you have any ability to "re-shape" the data at the source level and get Max and Mean in the same row (for each code / signal)?  Then it would be very easy to do what you want.

               

               

              If that's not possible, You could do an aggregated calculation: MIN(Max_Comp) / MIN(Mean_Comp)

              However, there are some limitations:

              1. You won't be able to create ad-hoc bins (you could do a calculated bin)

              2. You will have to make sure your view is at the level of code and signal (meaning that those two fields will have to be present in the view on Rows, Columns, Color, Level of Detail, etc... to define that as the level of aggregation.)

               

              I'm guessing that those limitations (which also exist for the original table calculation solution) will mean that it is not really going to be a good, viable solution.  So, if there is any way to re-shape the data and get Max and Mean in the same row, that really will be best.

               

              Regards,

              Joshua

              Row Level.png

              1 of 1 people found this helpful
              • 4. Re: How to compute a row from two?
                Vincenzo Manzoni

                While I was waiting your reply, I did what you suggested: I pivoted the source table to have mean and max on the same row.

                 

                Thank you again for your helpful answers!

                 

                Best,

                 

                - Vincenzo