1 of 1 people found this helpful
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.
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.
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)
Thanks a lot, much appreciated . I'll give it go with the data-set I have.