4 Replies Latest reply on Aug 2, 2017 4:58 PM by Abdul Subhan Mohammed

# Line Color Based on Increasing or Decreasing trend

Hi All,

I wanted to create a line where if the sales of present month is higher than previous month, then WHOLE line of that trend should be Green

and

If the sale of present month is lower than previous month, then the WHOLE line should be Red.

I have attached the sample store file where I have been looking for answers.

Anything even close, which you have used in your experience is also helpful.

Thank You

• ###### 1. Re: Line Color Based on Increasing or Decreasing trend

Try something like this:

IF WINDOW_MAX(IF SIZE() = INDEX() THEN SUM([Sales]) END) >= WINDOW_MAX(IF SIZE()-1 = INDEX() THEN SUM([Sales]) END)

THEN "Higher"

ELSE "Lower"

END

Place that calculation on color, and assign red and green as appropriate. The output can be a number or whatever text you want - I just picked higher and lower. The trick is to get a value that returns the same for the whole line, so we need a table calculation (though an LoD would work, although it's more complex). The above calculation uses WINDOW_MAX to find the maximum values in the whole sheet. The only values that are ever thrown are those in the last 2 datapointts - the rest will be null, so the max will always pull only the 2 we want.

• ###### 2. Re: Line Color Based on Increasing or Decreasing trend

Hi

I did this in 2 parts - first to determine the direction of the last segment of the line

if LOOKUP(sum([Sales]),last()) - lookup(sum([Sales]),(last()-1))  <0 then 1 else 2 end

Then to color the line in total

if [color line last segment]= 1 then "Red" elseif

[color line last segment]=2 then "Green" end

Then place the color of the last segment on detail and the color total line on color and you will get this Or Let me know if this is what you expected

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 3. Re: Line Color Based on Increasing or Decreasing trend

Thank You so much Jim,

I was able to get it working also with your approach but a single query is better than 2 and since we had one, I chose it as the correct answer. Yours is Correct as well !

Thank You so much for responding. I really appreciate your helping me out here.

Regards,

Abdul

• ###### 4. Re: Line Color Based on Increasing or Decreasing trend

Thank You so much Ben !

It worked!

Abdul