8 Replies Latest reply on Aug 1, 2017 8:18 AM by Ben Neville

    Different display attributes per label

    Melissa Stagnaro

      I have two labels.  One is the volume per year.  The second is the growth per year.  I am working with four years of data.

      I would like the volume to display only the first and last.  Marks to Label LINE ENDS.

      I would like the growth to display for the 2nd, 3rd, and 4th years.  Marks to Label ALL.

      I see how to do either both labels (volume and growth) are displayed for all years *or* both labels are displayed for first and last year (with first year growth being null), but I can not see how to have unique display attributes for each label.

      Thank you in advance.

        • 1. Re: Different display attributes per label
          Ben Neville

          Hi Melissa - good question. As you've noticed, there is no label option to have labels appear on on certain marks, and other labels to display on others. This means we need to be tricky with calculations. I'm going to assume you already have the underlying calculations (volume/growth) so I'll give you the calculation which assumes this.

           

          The solution is to detect whether we're actually at the line end, and display one measure in that instance, and display another in all cases. There are 2 options - I'll give you both and you can choose whichever you like:

           

          Option 1 - Indexes. This is essentially a positioning calculation which determines where you are in the table. You need to make sure this is set to Table Across or Down, depending on the orientation of your view (if it's a line chart, I'm hoping it's across). It gets a bit trickier if you had say, a line per customer or product line. In that case I'd use option 2 for simplicity, but you can make either work with partitioning.

          IF INDEX() = 1 OR INDEX() = SIZE() THEN SUM([Volume])

          ELSE SUM([Growth])

          END

           

          Option 2 is based on the actual years:

          IF WINDOW_MIN(YEAR([Date Field]) = MIN(YEAR([Date Field])) OR WINDOW_MAX(YEAR([Date Field]) = MAX(YEAR([Date Field])) THEN SUM([Volume])

          ELSE SUM([Growth])

          END

           

          Hope that helps.

          • 2. Re: Different display attributes per label
            Melissa Stagnaro

            Ben,

             

            Thank you for that.  I was having Tableau calculate the growth and the labels displayed correctly ((ZN(SUM([Volume])) - LOOKUP(ZN(SUM([Volume])), -1)) / ABS(LOOKUP(ZN(SUM([Volume])), -1)).

             

            It didn't like plopping that into the If Then Else so I saved that as "CalculatedGrowth" but was unsure what to use instead of SUM for "ELSE SUM([CalculatedGrowth])".  Error was Argument to SUM is already an aggregation and can not be further aggregated.

             

            IF INDEX() = 1 OR INDEX() = SIZE() THEN SUM([Volume])
            ELSE ([CalculatedGrowth])

            END

             

            I get "Can not mix aggregate and non-aggregate comparisons or results in IF expression".

             

            I would appreciate any further suggestions.  Thanks again

            • 3. Re: Different display attributes per label
              Ben Neville

              Hi Melissa - the first error is when you're applying an aggregate (SUM/AVG/COUNT/MEDIAN/etc.) to a field that is already aggregated. Tableau is telling you it can't re-aggregate that field. The second error is an issue that occurs when something in the calculated field is aggregated while something else is not.

               

              In your case, the calculation you pasted looks like the right answer to me I've tweaked it very slightly, but you should make [Calculated Growth] have the following logic:

              (ZN(SUM([Volume])) - LOOKUP(ZN(SUM([Volume])), -1)) / ABS(LOOKUP(ZN(SUM([Volume])),0))

               

              If it lets you save this without throwing an error on the calculation, this part is sorted. The calculation is definitely an aggregate as there are SUMs in addition to table calculations. You should then be able to use:

              IF INDEX() = 1 or INDEX() = SIZE THEN SUM([Volume])
              ELSE [CalculatedGrowth]
              END

               

              If there are no errors, you should be good to go. The only issue is that this is going to be either a number/decimal OR a percentage - it can't be both. So it will put the percentages as whole numbers or vice versa. One way around this is to weight your percentages appropriately by multiplying them as such:

              IF INDEX() = 1 or INDEX() = SIZE THEN SUM([Volume])

              ELSE [CalculatedGrowth] * 100

              END

               

              Then you just need to use some logic for the label to get it to apply the '%'. You could do that in the above calculation, but I prefer a second equation - call it 'Growth Label'. It should have the logic:

              IF INDEX() != 1 AND INDEX() != SIZE() THEN '%'

              ELSE ''

              END

               

              Getting multiple fields on label might be tricky if you've never done that before. The easiest way is to CTRL + select both fields, then drag them both to label at the same time. Next, you should be able to click on Label on your marks card, then the ellipsis (...) and arrange these to be on the same line in the order you like. You can take a look at the attached workbook for a mockup of what you need.

              • 4. Re: Different display attributes per label
                Melissa Stagnaro

                Thank you again.  Almost there!  Perhaps I should have mentioned earlier that I have grouped data.  Similar to mock data below.

                So the "10" and "8" are correct for first and last in total but I need first and last per pane.

                 

                I'd like to see 10, %, %, 5  |  14, %, %, 8

                 

                Dept A

                2013  10 widgets

                2014    12 widgets

                2015    8 widgets

                2016    5 widgets

                 

                Dept B

                2013  14 widgets

                2014    7 widgets

                2015    22 widgets

                2016    8 widgets

                 

                Sorry but not allowed to open your attachment.

                2017-07-31_15-26-35.png

                • 5. Re: Different display attributes per label
                  Ben Neville

                  Do a search on table calculations and partitioning. The calculations should all still work - you just need to partition correctly. Here's a good start.

                  • 6. Re: Different display attributes per label
                    Melissa Stagnaro

                    Thank you so much!  I will check out that link and get more proficient. 

                    • 7. Re: Different display attributes per label
                      Melissa Stagnaro

                      Read the article this morning and now graph looks nice.  Thank you so much.