3 Replies Latest reply on May 10, 2018 10:39 PM by Shehan Gunawardena

    How to show just the negative slope of a line chart?

    Shehan Gunwardena

      I want to know how to show only the lines shown in the black circles. I wrote a calculation to get values of 'dips' (e.g: Feb, April, July, oct, dec), but those are just point. I can't figure out how to make the line.

       

      I want this so i can make the negative parts of the line chart to be an area (With use of dual axis). Like this

        • 1. Re: How to show just the negative slope of a line chart?
          Jennifer VonHagel

          Hi Shehan, here's one idea.

           

          I created a Counter field to group values that have only a negative trend. You can see that each time the Sales is higher than the previous row, the Counter increases by 1. If Sales is less than the previous row, the counter stays the same.

          Now in a chart - we can group by the Counter field so that the correct time periods are shown together: The counter field is pointed out here so you can see it, but you can hide the header in the final data.

           

          The Counter calculation is this.

          RUNNING_SUM(

              IF FIRST() = 0 THEN 1

              ELSEIF SUM([Sales]) < LOOKUP(zn(SUM([Sales])),-1) THEN 0

              ELSEIF SUM([Sales]) > LOOKUP(zn(SUM([Sales])),-1) THEN 1

              ELSE 0 END

          )

           

          First, it assigns a 1 or 0 to each record of the table. If it is the first record of the table, it is 1. If the next record's sales are < the current record, make it 0. If the next record's sales are > than the current records, make it 1.

           

          Then the Counter does a Running SUM of these 1s and 0s, giving the result shown.

           

          Then, set up your chart - the rest is really just formatting. Here, I chose to do a dual axis so I could show bar and area charts combined. Bars are shown for each month, and the area connects bars within a Counter group. See how there are some months that are not connected to other months? If this were only an area chart without the bars, they would be so skinny they are hard to see.

           

          I hope this helps.

           

          Jennifer

          1 of 1 people found this helpful
          • 2. Re: How to show just the negative slope of a line chart?
            Jennifer VonHagel

            Also, if you want the chart NOT to show the bars where there was a positive increase over the past month and no decrease in the next month, you could adjust your Sales column to replace these values with NULL:

            Sales Adjusted:

            IF SUM([Sales]) > LOOKUP(zn(SUM([Sales])),-1) AND

               SUM([Sales]) < LOOKUP(zn(SUM([Sales])),1) 

            THEN NULL

            ELSE SUM([Sales])

            END

             

            Thanks,

            Jennifer

            • 3. Re: How to show just the negative slope of a line chart?
              Shehan Gunawardena

              Hi Jennifer,

               

              Thanks a lot, much appreciated . I'll give it go with the data-set I have.

               

              Regards,

              Shehan