9 Replies Latest reply on Sep 11, 2015 5:38 AM by Rody Zakovich

    Is it possible to rename a single bar in a bar chart?

    Bill Bosworth

      I'm trying to rename the last bar in a chart to accurately reflect the time period (from Q3 to Jul/Aug).  Is this possible?

      Rename Single Bar.JPG

        • 1. Re: Is it possible to rename a single bar in a bar chart?
          swaroop.gantela

          Bill,

           

          Please see if the attached may be a starting point for you.

          It may not meet your requirements, because I converted the continuous

          dates into a discrete string.

           

          IF MAX([Order Date])=WINDOW_MAX(MAX([Order Date]))

          THEN STR(YEAR(MIN([Order Date])))+" Jul/Aug"

          ELSE MIN(STR(YEAR([Order Date]))+" Q"+STR(DATEPART('quarter',[Order Date])))

          END

           

          Also, I noted that you have those weird marks on the last bar.

          You may want try turning off graphics acceleration:

          What causes the "quotation marks" in Tableau graphics?

          • 2. Re: Is it possible to rename a single bar in a bar chart?
            Daniel Vincent

            Here is my stab at this...

             

            IF LAST()=0 THEN

            Max(DATENAME("year",[Order Date])) + " " + (MAX(DATENAME("month",[Order Date]))+"-"+MIN(DATENAME("month",[Order Date])))

            ELSE ATTR(DATENAME("year",[Order Date])) + " Q" + attr(DATENAME("quarter",[Order Date]))

            END

             

            It will dynamically change the last column to 1,2,3,etc months.

            1 of 1 people found this helpful
            • 3. Re: Is it possible to rename a single bar in a bar chart?
              Bill Bosworth

              Thank you Swaroop and Daniel - not as straightforward as I was hoping, but both solutions work.  Thank you both very much.

              Bill

              • 4. Re: Is it possible to rename a single bar in a bar chart?
                Rajeev Pandey

                Dear Swaroop,

                 

                could you please explain me the below part .Although the solution worked perfectly but I am confused with Logic which you applied. It would be great if you can throw some light on this.

                 

                IF MAX([Order Date])=WINDOW_MAX(MAX([Order Date]))

                THEN STR(YEAR(MIN([Order Date])))+" Jul/Aug"

                 

                Confusion 1:why you have taken "(MIN([Order Date])))" , why not Max([Order Date]))) because the (MIN([Order Date]))) will give you 2012 as per your Filter Condition. As per requirement , we need to take last year 2015 Q3.

                 

                Confusion 2:IF MAX([Order Date])=WINDOW_MAX(MAX([Order Date])) , this will always be "TRUE" , then how calculation is jumping to your else part .

                 

                ELSE MIN(STR(YEAR([Order Date]))+" Q"+STR(DATEPART('quarter',[Order Date])))

                END

                • 5. Re: Is it possible to rename a single bar in a bar chart?
                  Rajeev Pandey

                  Dear Daniel,

                   

                  Could you please explain me how your formula is dynamically changing the last column into 1,2,3 months.

                  When I am running the below calculation .I am getting 2014 September-april. How come you are getting oct-dec


                   

                  Max(DATENAME("year",[Order Date])) + " " + (MAX(DATENAME("month",[Order Date]))+"-"+MIN(DATENAME("month",[Order Date])))

                  • 6. Re: Is it possible to rename a single bar in a bar chart?
                    swaroop.gantela

                    Rajeev,


                    I was trying to use ATTR() to aggregate, but it wasn't working for this setup.

                    For point 1, I think either min or max will serve the same purpose because of the way I'd setup

                    the quarters. My dates are different from the requirement because I was using a mock SuperStore.

                    For point 2, I think it has to do with way I'd used discrete groups of quarters. If the max date in that quarter

                    is equal to the overall window max, then execute first part.

                    • 7. Re: Is it possible to rename a single bar in a bar chart?
                      Rajeev Pandey

                      If I am not asking for too much. Would you mind taking out a few minutes, at your convenience and clarify my doubt. Sorry for being Incomprehensible.

                      As per definition, WINDOW_MAX(expression, [start, end])

                      Returns the maximum of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.

                      For example, the view shows quarterly results. A window maximum within the Date partition returns the maximum value across all dates. So My confusion over here is how "MAX([Order Date])=WINDOW_MAX(MAX([Order Date]))" value leading to the Last column ( or Last()=0). How we are finding the Max dates among the dates . Let say if we are using WINDOW_MAX(sum([Sales]), [First],[ Last]) then  window maximum within the Date partition returns the maximum sales across all dates.

                       

                      Also, How min and max are behaving same way as per your previous comments

                      • 8. Re: Is it possible to rename a single bar in a bar chart?
                        Daniel Vincent

                        I would say right off the bat if you are getting different values you are either using a different data source or more likely did not set the compute using of the window calc.  My table calc is looking for the last index() so I need to compute using across.

                        • 9. Re: Is it possible to rename a single bar in a bar chart?
                          Rody Zakovich

                          Hi Bill,

                           

                          My approach is similar to Swaroop, but I don't use any table calcs

                           

                          IF MAX(DATETRUNC('month', [Order Date])) != (MAX(DATEADD('month', -1, DATEADD('quarter', 1, DATETRUNC('quarter', [Order Date])))))

                          THEN

                              IF DATENAME('month', MIN([Order Date])) = DATENAME('month', MAX([Order Date]))

                              THEN STR(YEAR(MAX([Order Date]))) + ' ' + DATENAME('month', MIN([Order Date]))

                              ELSE STR(YEAR(MAX([Order Date]))) + ' ' + DATENAME('month', MIN([Order Date])) + '/' + DATENAME('month', MAX([Order Date]))

                              END

                          ELSE STR(YEAR(MAX([Order Date]))) + ' ' + 'Q' + DATENAME('quarter', MAX([Order Date]))

                          END

                           

                          Instead what we do is a little Date trickier. If the MAX month within the Partition (In this case QUARTER DISCRETE) is not equal to the MAX MONTH in that quarter, then check to see if the MIN MONTH = MAX MONTH. If it does, just return the single month. Else Return the Min and Max Month.

                           

                          We can determine this by the first formula

                           

                          MAX(DATETRUNC('month', [Order Date])) != (MAX(DATEADD('month', -1, DATEADD('quarter', 1, DATETRUNC('quarter', [Order Date])))))

                           

                          We simply truncate down to quarter, add a  quarter and subtract a month, to get the Last month in that particular quarter.

                           

                          This allows us to have a dynamic end label.

                           

                          9-11-2015 8-36-28 AM.png

                           

                           

                          While only in the First Month

                           

                          9-11-2015 8-37-22 AM.png

                           

                          Bonus here is you don't have to worry about Table Calcs and partitioning. PLUS it is dynamic!

                           

                          Hope this helps.

                           

                          Regards,

                          Rody