4 Replies Latest reply on Apr 27, 2012 6:07 AM by benoit.declarens

    Using Quick table calculation as column

      Hi,

      i'd like to know if there is a simple way to use quick table calculation (like Difference,etc...) as column?

       

      I'd like to use a list of measure name in row and in column the values associated for year N, N-1, the difference, growth.

      It's ok for N,N-1 value but not for difference an growth.

      Below an example :

       

      sample.png

        • 1. Re: Using Quick table calculation as column
          Jonathan Drummey

          Hi Benoit,

           

          You can have table calculations return either text or numeric discrete (blue pill) values that will cause Tableau to generate headers. You can also use the special Measure Names/Measure Values pills to get data, for example for your view you'd have Store and the Store measures on the Rows shelf, and Product Type and Measure Names on the Columns shelf, with Measure Values on the Text shelf of the marks card. There would be 4 measures on the Measure Values card that you would have to create (I'd start with the Quick Table Calculations and then customize them): N-1, N, Growth, and Difference.

           

          If you need more assistance, I suggest posting a packaged workbook with sample data.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Using Quick table calculation as column

            Hi Jonathan,

            thank you for your explanations. I've tryed to solve my pb with Measure Names and Measure Values but it doesnt feet my needs.

            Here is a sample with the database joined if you can have a look and an idea...

             

            Thank you for your help,

             

            Regards,

            Benoit

            • 3. Re: Using Quick table calculation as column
              Jonathan Drummey

              Hello Benoit,

               

              OK, having looked at your data I can see where Measure Names isn't meeting your needs. Tableau only allows one instance of Measure Names/Measure Values in the view, and it seems like you'd need two, in a way, one for the measures on Rows (as you have in the attached workbook) and another for the N, N-1, Diff, and Growth calculations on Columns.

               

              It's also possible to drag additional axes to generate fake headers, as in this technique here:

              http://community.tableau.com/message/171852, however you'd need to have a different calculation for each row because Measure Names is on rows, and unfortunately Tableau doesn't let you access Measure Names/Measure Values inside a calculated field.

               

              So the only thing I can think of is to use duplicate data so that there is one data set for each measure (stock and Nb mag). There might be a way to pull this off using a blend, what's easier for me is to use Custom SQL to generate a UNION query. I took the datasource you'd created, duplicated it, and then edited the query to return all the existing data twice, with an extra field called "Measure Type" that has the values Stock Measure or Nb mag Measure.

               

              Then I re-created your crosstab, with the Measure Type now on the view and Year(Periode) on the Level of Detail. Then I put Measure Names on columns (as I'd originally indicated) and created 4 calculations that you can see in the attached. The tricky bit is the Diff and Growth calcs. Here's the Diff calc:

               

              IF FIRST()==0 THEN

                  WINDOW_MAX([N])-WINDOW_MAX([N-1])

              END

               

              Because Year(Periode) is on the Level of Detail there are multiple rows of data (one for each year) behind the one value, we use the IF FIRST()==0 bit to hide extra rows. We use WINDOW_MAX([N]) to get the non-Null/blank value of N, since N is actually returning two values, one non-Null value for the year 2010 and a Null/blank one for the year 2009.

               

              The Diff and Growth calcs both have their Compute using set to Period to get the correct results.

               

              Jonathan

              • 4. Re: Using Quick table calculation as column

                Hi Jonathan,

                thank you very much for you answer : THAT IS what I needed ! I did'nt think it was necessary to use indicators a dimension member in the fact table (it will duplicate my data by n indicators...). By the way it works so ...nevermind for database size.

                 

                Best regards,

                Benoit