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?

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?

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?

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:

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?

Hi Jennifer,

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

Regards,

Shehan