4 Replies Latest reply on Feb 16, 2016 6:04 AM by Simon Runc

    Blank lines with Table Calc Difference

    Julia Hennelly

      I am sure this is not difficult to do but for some reason I am having a very hard time doing it.  This is something that is needed in most of my analyses.  I shall attach a workbook but first picture is what I need to achieve (not too worried about formatting but need a worksheet showing this data as a means of validation.

       

      Here is my attempt in Tableau

       

      This would work if I could get rid of the 2015 line of zeros???

       

      Workbook attached.

       

      Julia

        • 1. Re: Blank lines with Table Calc Difference
          Simon Runc

          hi Julia,

           

          Just looking through some unanswered questions and came across your one!...

           

          Unfortunately I'm not able to use the attached .twbx as the data connection is still Live to your MySQL instance...if you 'extract' the data and resend I'll be able to open it.

           

          However I think you should be able to achieve what you want by wrapping your formula in an IF statement. The 'blanks' you are after are for NULLs, so by changing all Zeros to NULLs we should get the affect you want.

           

          something like

           

          IF [You Diff Formula] = 0 THEN NULL ELSE  [You Diff Formula] END

           

          Hopefully that does the trick, but if not please re-attach 'extract' version of your .twbx and I'll be happy to take a look.

          1 of 1 people found this helpful
          • 2. Re: Blank lines with Table Calc Difference
            Rody Zakovich

            Hello Julia,

             

            Just wanted to comment on this, and hopefully save you from headaches in the future.

             

            Tableau is a Data Visualization tool at its' core. It works best when you are creating visual representations of data (I.e. Bars, Lines, etc).

             

            What Tableau is not great at, is re-creating Excel like Crosstabs. There are a lot of limitations on what we can do as far as conditional formatting, and cell based calculations (Both are possible, but can get complex).

             

            If the goal is to create Ledger like crosstabs, I highly suggest leaving it in Excel, and then use Tableau to visualize it.

             

            Here is a great discussion on topic

             

            Cross Tab Reporting vs Visual Analytics

             

            Best regards,

            Rody

            1 of 1 people found this helpful
            • 3. Re: Blank lines with Table Calc Difference
              Tharashasank Davuluru
              1 of 1 people found this helpful
              • 4. Re: Blank lines with Table Calc Difference
                Simon Runc

                hi Julia,

                 

                ...just re-read the question following Rody's comments! (and mis-read this as you wanting to remove the zeros, and not the entire row!!) and firstly he is right in terms of replicating 'Excel style' cross tab reports in Tableau.

                 

                If you do want to achieve this have a look at my response here on how you can (partly) achieve this

                Re: How to display year on year growth as label on area chart without displaying previous year data

                 

                They key here, is that to 'hide' a row (in your case the 2015 row) but still let Tableau have access to that year to perform the YoY calculation, I've used a Table Calculation (index()) as a filter and due to the 'Order of Operations' this has the affect of filtering the view but not the 'underlying data'. However this will hide the entire 2015 Row...and in your example you want to show it for the 'actual values', but hide it for the variance measure. Like most things in Tableau we can work around this, but it soon becomes complicated (and pretty inefficient).

                 

                ...Let me know what you decide and if you want to try this in Tableau, I'll take another look.

                1 of 1 people found this helpful