5 Replies Latest reply on Jul 13, 2018 1:37 PM by Glenn Maples

    Changing the x-axis date format dynamically on a line chart with missing values

    Nicolas Beuchat

      Dear Tableau community,

       

      I am trying to build a line chart with the time on the x-axis where we can switch between a daily, weekly, monthly, and yearly view using a parameter control. I would like the formatting of the x-axis to be dependent on the parameter control. I want to display the count of people and the count of actions over time but for certain days, there aren't any data.

       

      Desired output

      Ideally, I would like the following charts when switching from a daily to weekly view, taking into account the missing values.

      Current status

      So far, I could successfully change the date level dynamically (as described here: http://kb.tableau.com/articles/knowledgebase/changing-date-level-dynamically). The problem is that I have to chose "Exact Date" to format the time axis, which does not work for my purpose. Apart from formatting issue of the x-axis, I have an issue for displaying missing values:

       

      Discrete - daily

      Discrete - weeklyContinuous - dailyContinuous - weekly

      With discrete time value and the daily view, it works fine and missing values are displayed as 0

      With discrete time value and the weekly view, all dates between the start of the week are displayed as 0With continuous time value and the daily view, the line connects only existing points, hiding the missing valuesWith continuous time value and the weekly view, it works fine

       

      I have attached a packaged workbook with some example data. Is it at all possible? One option would be to duplicate the worksheet and hide them depending on the parameter control. However, I have 20+ of such worksheet which would not be manageable.

       

      I am using version 9.3 of Tableau here (but need the solution to work in 9.2 as well)

       

      Thanks a lot in advance for your help!

      Nicolas

        • 1. Re: Changing the x-axis date format dynamically on a line chart with missing values
          Sherzodbek Ibragimov

          Nicholas,

          I hope I understood your requirement. Is that something you want to achieve?

           

          • 2. Re: Changing the x-axis date format dynamically on a line chart with missing values
            Sherzodbek Ibragimov

            As you wanted, I left axis with exact day. If it is the look you want, please see below steps:

            - Right click axis and Format

            - From Fields, choose UniqueAction

            - Choose Pane

            - Under Special Values, for Marks choose 'Hide (Connect Line)'

            Do the same thing for UniqueName.

            Or see below pics:

             

            Please let me know if that is what you want, also you may want to play with this options to get the exact result you want by selecting other options.

            Thanks

            Sherzod

            • 3. Re: Changing the x-axis date format dynamically on a line chart with missing values
              Nicolas Beuchat

              Hi Sherzod,

               

              Thanks a lot for your quick answer! Unfortunately, this does not work as I would like to. The problem here is that on February 7 and February 8, there are no data which means that the line should go to zero. Wrong decisions can be made by the business if the chart connects the points ignoring the missing values.

               

              I can make it work for the daily view if I use a discrete variable and "Show Value at Default". However, if I switch to the weekly view, it looks like the second image in the table in my original post.

               

              Really, the only thing I would need is to change the formatting of the x-axis based on the parameter control. The only solution I currently found is to create different worksheet and switch between them. However, this becomes tremendously difficult to maintain.

               

              Any other suggestion?

               

              Thanks for your help,

              Nicolas

              • 4. Re: Changing the x-axis date format dynamically on a line chart with missing values
                Jagjit Singh

                Hi Nicolas,

                 

                Did you get a solution?

                 

                 

                Thanks

                Jag

                • 5. Re: Changing the x-axis date format dynamically on a line chart with missing values
                  Glenn Maples

                  I know this is old, but one way to do this is just not to have missing dates.  You can achieve this in SQL, or I think with some work in EXCEL by:

                   

                  1) Get the values of all possible variables (for example: Create table division as select distinct division from my table

                                                                                                           Create table state as select distinct state from my table

                                                                                                           Create table date as select distinct date from my table

                   

                  2)Create a Cartesian product. 

                   

                  CREATE TABLE PADDING_DUMMY_DATA

                  AS

                  SELECT DISTINCT Division, STATE, DATE,  0 as REC_CNT

                  FROM Division

                  CROSS JOIN state

                  CROSS JOIN date

                   

                  Note (depending on the SQL variant) you don't need to include a join condition. The cartesian product gives all possibility and initializes your "counting variable" to 0.

                   

                  3) Finally:  add this dummy data to your "real" data in a union.  Again the syntax may be a bit dependent on your sql variant.  This works because---assuming your real data looks something like

                   

                                 Division     State          Date           Rec_cnt

                                 Top               TX          1/1/2020          10

                   

                  --the union will only put in dummy data if there is no real data for the particular combination of Divisions, State, Date

                   

                  CREATE TABLE PADDED_FINAL_OUT

                  AS SELECT * FROM (

                  SELECT * FROM Real_data

                  UNION

                  SELECT * FROM PADDING_DUMMY_DATA

                   

                   

                  Hope this helps someone