3 Replies Latest reply on Mar 9, 2016 10:24 AM by Cathy Bridges

    Difference Between Two Measures Showing Blank

    Owen Mattison

      Hi guys!

       

      I am trying to compare current period vs. prior period numbers and created two calculated measures to do so.  These two calculated measures are essentially "boxes" because they display different measures based on a parameter selector (sales, installs, etc.). These numbers are also controlled by a second parameter that determines the periods you are comparing (months, quarters, years, etc).  I created a third measure to calculate the difference between the two, despite what the "boxes" contain.  Tried by [Measure 1] - [Measure 2] and sum([Measure 1]) - sum([Measure 2]) but both turn up nulls in the 3rd column.  I thought this would be the easiest part of development, but it is actually proving quit tricky.  Thoughts?

        • 1. Re: Difference Between Two Measures Showing Blank
          Steve Mayer

          It would help if you would include a packaged workbook with your post, but I'll take a stab based on the information you provided. Try the following:

           

          SUM(ZN([Measure 1])) - SUM(ZN([Measure 2]))

           

          Sometimes there are NULL values in one or the other measure that cause the whole calculated field to compute to NULL. Putting a ZN around it will return 0 for NULL rows, so it shouldn't affect your calculation.

           

          If this doesn't work, please post a book,

           

          -Steve

          • 2. Re: Difference Between Two Measures Showing Blank
            Rody Zakovich

            Hi Owen,

             

            You'll most likely want to wrap the ZN function around the aggregate function.

             

            I'm assuming you are working with sparse data, in some form or another. So your individual values are not what is causing this to be NULL (Aggregates ignore individual NULL records much like a DB), but the Values of the aggregate in relation to your vizLoD.

             

            ZN(SUM([Measure 1])) - ZN(SUM([Measure 2]))

             

            Depending on your situation, pill arrangement, you may need to enforce Densification in order for the marks to properly show.

             

            Data Densification — Joe Mako

             

            If you can share an example workbook, that would be very helpful.

             

            Regards,

            Rody

            • 3. Re: Difference Between Two Measures Showing Blank
              Cathy Bridges

              I often need to do this kind of calculation, although I haven't attempted it using a parameter to select whether I'm comparing months, years, etc.

               

              I think the trick is to look at your column header... how are you telling Tableau to distinguish the current period vs the prior period?

               

              In my case, I use a calculated field "CYPY" that tells Tableau if the date is less than a year ago it's "CY", and if it's more than a year ago but less than 2, it's "PY". (I use a parameter to set the relative end date, since you can't use MAX([Date]) in calculated fields.)

               

              So when I make my table calculation to determine the difference, I have to set it to calculated using "CYPY" relative to "PY". Then I can either hide the PY column altogether or just know that the % difference for PY will always be zero.

               

              I've attached a workbook to show you what I mean.

               

              YoY1.png

              YoY2.png