12 Replies Latest reply on Jan 13, 2014 5:26 PM by Wendy Zhou

    Change x-axis according to selection

    Prashant Sharma

      Hi all,

       

      I wanted to create a graph in which i wanted to change the x-axis according to the range to dates means if we select range of year for last 3 years then x-axis will show only year & if i select range of month for last 3 months then it will show months on x-axis. Attaching two images: -

      Year_Wise.jpg

      In this when i select last 3 years from the quick filter then it will show me only year. Month_wise.jpg

      In this when i select the month for last three years, x-axis will show me only month with year, not year.

       

      I want a quick filter which is showing relative ranges but when i select year then x-axis will show me only years & when i select month then it will show me month.

      I worked a lot on this but i do not find any solution on this. Is it possible or not?

        • 1. Re: Change x-axis according to selection
          Mark Holtz

          Hello Prashant,

          I don't think you can get to what you're after using solely a quick filter.

          If you want to change the grouping of the x-axis, you can just add a parameter + calculated field combination to use in conjunction with your date filter.

           

          1) Create a parameter "Select Date Range Type" as a String data type with list options:

          "Year" and "Month" (and and "Day" if you want).

           

          2) Create a calculated field that will return the x-axis value based on the parameter selection. Unfortunately in Tableau, you cannot make formatting dependent on parameter selection, so your best bet is probably to make your calculated field result in a text string that has the relevant date information formatted how you want it. This means that for the month values will order alphabetically, e.g., "December 2012" will precede January 2012".

           

          So, I guess what I'm saying is that this solution requires you to create 2 calculated fields. One for ordering correctly and the other for the display value. (You could also possibly do this using aliases if you don't have too many values, then you can just create the X-Axis Order field I describe next.)

           

          For your X-Axis Order field (should be the first thing on your Columns shelf, you can right click the pill and uncheck "Show Header"), your calculated field would be:

          IF [Select Date Range Type] = 'Year' //YEAR VALUES

          THEN STR(DATEPART('year',[Date]))

          //MONTH VALUES

          ELSEIF [Select Date Range Type] = 'Month'

          THEN STR(DATEPART('year',[Date]))

              +'-'+

              //add a '0' in front of months number 1-9, otherwise use 2 digit month number

              IF DATEPART('month',[Date])<10

              THEN '0'+STR(DATEPART('month',[Date]))

              ELSE STR(DATEPART('month',[Date]))

              END

          //DAY VALUES

          ELSE STR(DATEPART('year',[Date]))

              +'-'+

              //MONTH add a '0' in front of months number 1-9, otherwise use 2 digit month number

              IF DATEPART('month',[Date])<10

              THEN '0'+STR(DATEPART('month',[Date]))

              ELSE STR(DATEPART('month',[Date]))

              END

              +'-'+

              //DAY add a '0' in front of days number 1-9, otherwise use 2 digit day number

              IF DATEPART('day',[Date])<10

              THEN '0'+STR(DATEPART('day',[Date]))

              ELSE STR(DATEPART('day',[Date]))

              END

          END

           

           

          The X-Axis Formatted field would be:

          IF [Select Date Range Type] = 'Year' //YEAR VALUES

          THEN STR(DATEPART('year',[Date]))

          //MONTH VALUES

          ELSEIF [Select Date Range Type] = 'Month'

          THEN STR(DATENAME('month',[Date]))+'-'+STR(DATEPART('year',[Date]))

          ELSE STR(DATEPART('month',[Date]))

              +'/'+

              STR(DATEPART('day',[Date]))

              +'/'+

              STR(DATEPART('year',[Date]))

          END

           

          I have attached a sample. Hope that helps.

          1 of 1 people found this helpful
          • 2. Re: Change x-axis according to selection
            Prashant Sharma

            Thanks Mark,

             

            What i asked is not possible in Tableau. I already provided solution like yours but you know sometime requirements are very unusual. Thanks for writing your answer. I am unable to find any solution of my question.

            You used a very long formula. I think with the case statement it will be more simple. Check my attached file.

             

            Thanks again dear.

            • 3. Re: Change x-axis according to selection
              Edwin Mulianto

              Mark,

               

              The x-axis order is a string and therefore cannot be used for line graph  Any way around it?  Tks!

              • 4. Re: Change x-axis according to selection
                Jackie Klein

                I need to do the same thing as Edwin - use the custom x-axis on a line graph.  Any solutions for this?  Thx!

                • 5. Re: Change x-axis according to selection
                  Mark Holtz

                  One thing you can potentially do is convert the dates into integers instead of strings.

                  So 7/18/2013 becomes 20130718.

                  I typically do this with a formula like:

                  YEAR([Date])*10000+MONTH([Date])*100+DAY([Date])

                   

                  But, it makes for an ugly label...

                   

                  Another option is to create separate views for each different date granularity and use a parameter to select which one you want. Then just use containers on a dashboard to swap out the views based on the date granularity selected via the parameter. Similar to what is outlined in this knowledge base article: Creating a Sheet Selector for a Dashboard | Tableau Software

                  • 6. Re: Change x-axis according to selection
                    Jackie Klein

                    Ok - so I think I can live with the bars:)  That sounds like a lot of work!

                     

                    Now I'm working with the x-axis regrouping.twbx workbook from above.  Instead of the Day option, I want a week option that will display the first date for the week on the x axis when Week is selected from the Parameter.  I've been trying to modifying it myself unsuccessfully and hoping you can help me with that.  Thanks!

                    • 7. Re: Change x-axis according to selection
                      Mark Holtz

                      Hi Jackie,

                       

                      Can you attach your workbook to your post? There were more than 1 above...

                      • 9. Re: Re: Re: Change x-axis according to selection
                        Mark Holtz

                        Jackie,

                         

                        Rather than mess with all the re-formatting, this is more easily done using view-swapping. View swapping is the easiest way to allow a user to toggle the formatting that is used, or even graph type (bar to line), etc.

                         

                        I have included the steps on the "title/instructions" tab in the attached workbook. And as a bonus, you can use this method and still make line charts if you want!

                         

                        Does that get you what you're after?

                        • 10. Re: Re: Re: Change x-axis according to selection
                          Jackie Klein

                          Thanks Mark.  This would work great in theory - however I am using a table calc for a filter on each of the Day, Week, Year sheets.  The reason I am doing this is because I am using line graphs that have two lines on them - one for the Team Member and one for the average of the Team.  I don't see a way to make my Team Member filter which is a table calc global.  I do not want to create a parameter instead because there are hundreds of team members and new one's get added as often as Daily.  Any ideas?

                          • 11. Re: Change x-axis according to selection
                            Wendy Zhou

                            I'm heading to the same question here. My client want to see monthly chart deep into weekly chart.

                            I thought this could be done, by create weekly set data and monthly set data. However, calculation didn't go through. I wonder how this could be done..?

                            • 12. Re: Change x-axis according to selection
                              Wendy Zhou

                              I have to say this's awesome! it works great. I just wish later on tcc14, Tableau could do some changes on this.