9 Replies Latest reply on Nov 13, 2017 12:49 AM by Donna Coles

    Change line type and remove 0 value

    daniel.andell

      I have a chart showing three years of sales until YTD, you see my setup below. Now I would like to do two things I don’t find a solution to since the way I put it togather doesnt match other examples I have found.

       

      1. Change the line type to dashed for the two previous years.
      2. I would like the chart to remove the 0 value for December and instead cut the line from the first and last active month.

       

       

      1.jpg

        • 1. Re: Change line type and remove 0 value
          Donna Coles

          Hi Daniel

           

          1) Currently there is no option in Tableau to set the style of a line mark type to be dashed.  I have seen some posts where people have managed to achieve something that makes the display looked dashed, but it will overcomplicate your solution.

           

          An alternative to enhance the latest year to the previous years is to add the Year onto the Size shelf too and make your latest year the thickest.  We've found this along with the graduating colour to be an effective representation.

           

          2) I'm not entirely sure what you mean by first & last active month, but when we're in the middle of a month and so only have partial month data that I don't want to show, I do the following

          a) create a calculated field (Start of Current Month) that's the 1st day of the current month we're in : DATETRUNC('month', TODAY()) . So if today is 8th November 2017, then this calc would return 1st November 2017.

          b) create a calculated field 'Exclude Current Month' as [Date Field] < [Start of Current Month] then add to Filter shelf and set to True.  Only records where the date is before 1st November would then be plotted.

           

          Hope this helps

           

          Donna

          • 2. Re: Change line type and remove 0 value
            daniel.andell

            Thanks Donna

            I have a filter to select year, quarter or month. When I change my selection all months that has no value is showing 0. I would like the line to start from the first Active value and end on the last. So in the example below for quarter it should only have a line between october and november. Not sure if your example solves this.

             

             

            1.jpg

            • 3. Re: Change line type and remove 0 value
              Donna Coles

              Hi Daniel

               

              You could try adding SUM([Sales]) is at least 1 to the filter shelf perhaps, but if there's a 0 value between active months, this might not give you what you want.  A packaged workbook would help as I've got an idea, but don't want to make assumptions about how your data is structured and what all your filters are (they've been cut off in the image).

               

              Donna

              • 4. Re: Change line type and remove 0 value
                daniel.andell

                Thanks for helping me Donna

                I have attached an example. I have the exact same problem here. As you see on Sheet 2 i dont have any sales in December and therefore I would like the line to stop on November.

                • 5. Re: Change line type and remove 0 value
                  daniel.andell

                  I have another example. Now I added the last two months total for the two previous years. So basically, I compare YTD with same period for two years. I also see total value for the last two years.

                  Now I just want to remove the 0 value for December for the YTD.

                  And if possible I would like to dash the two lines that is continuing for the last two years.

                   

                  1.jpg

                  • 6. Re: Change line type and remove 0 value
                    daniel.andell

                    As you see in Sheet 2 I have months with no value. But I dont want to remove the month completely just not show a line when the value is 0

                    1.jpg

                    • 7. Re: Change line type and remove 0 value
                      Donna Coles

                      Hi Daniel

                       

                      Apologies for delayed reply - been pretty busy.

                       

                      In response to your last issue where your December value are all 0, then replacing the 0 with NULL will achieve what you need, but whether this works for all your scenarios... eg something along the lines of

                      Create similar calculated fields for Prior2 and YTD, then plot these 'revised' fields instead and you get

                       

                      Donna

                      • 8. Re: Change line type and remove 0 value
                        daniel.andell

                        Thanks Donna I really appreciated your help, it works great now.

                        • 9. Re: Change line type and remove 0 value
                          Donna Coles

                          Great - glad you're sorted!