2 Replies Latest reply on Oct 3, 2016 11:09 AM by Ashleigh Hawkins

    Change sign on aggregate count

    Ashleigh Hawkins

      Hi everyone

       

      I have attached an example worksheet of what I am trying to achieve:

       

      NEW - This is the count distinct number of departments that appear in December (filter) that have not appeared in previous months

      EXITED - This is the count distinct number of departments that have disappeared in December - so they were in previous months but no longer appear in December

       

      The table is showing both values as positive. I would like the NEW to be positive and the EXITED to be negative which makes the grand total give me the NET figure

       

      Any help most welcome

       

      Thanks

      Ashleigh

        • 1. Re: Change sign on aggregate count
          Joe Oppelt

          You have to force the change.

           

          I provided one way in the attached.

           

          There are some things you have to check out in this.

           

          As you can see in your original Sheet 1, because the display field is an aggregate, the automatic TOTAL feature doesn't handle it well.  To get around this, I always create my own totals sheet, and then position it on a dashboard so that it looks like one VIZ to the user.  So you'll see in Sheet 3, I took off the "Column totals" from the sheet.  Then in Sheet 2 I created my own total.

           

          Some info about Sheet 2.  You'll see that I left [New_Exited_Filter] on the sheet in the Details shelf.  It need to be on the sheet so that tableau still has visibility to that dimension to calc out the values in the new display calc.

           

          Also, you'll see an INDEX calc on the filter shelf.  Take it off temporarily to see why we have that there.  You'll see that three total values display without the filter because it gets calc'd three times for the respective values of [New_Exited...]  So I put INDEX on the filter shelf to display only one of these values.  Hit the back-arrow to put the filter back.  Right click on the filter pill and choose edit table calc.  I run this along [New_Exited...] because that's what creates 3 copies of the value.  Edit the filter itself and you'll see that I choose min- and max-value of 1.  Just get the first one.

           

          Look at the dashboard.  I positioned the two sheets so that the total tucks under the data sheet.  I changed the month quick filter to apply to all sheets (so that whatever you pick for the data sheet also gets reflected in the totals sheet.)  If the number od New-Exited values changes as you select different months, the total sheet stays tucked under the data sheet.  This works because I have them positioned in a container on the dashboard.

           

          Hope I explained all that OK.

          • 2. Re: Change sign on aggregate count
            Ashleigh Hawkins

            Hi Joe

             

            Thanks - that certainly did the job

             

            Ashleigh