4 Replies Latest reply on Jun 16, 2016 9:20 AM by Mark Fraser

# Calculated field to change colour of Line Graph

Hello Guys,

So, currently I have a line graph with the dates along the X axis and the number of Records along the Y. The line graph is going up and down. What I would like it to do (when the line goes from point to point) is when it goes up I want the colour of the line to turn RED, but then the line goes down to the next point I want it to go GREEN.

I am looking to create a calculated field that will give me a GREEN or RED value which I can then colour my line graphs with.

The GREEN or RED would be based on whether the number of records is going up or is going down.

I need to write something like this in a calculated field i guess:

IF (Number of Records) is higher than the previous point THEN 'RED'

ELSEIF (Number of Records) is lower than the previous point THEN 'GREEN'

PS. I cannot send a copy of my workbook as it is a work machine and it's also hard to recieve TWB files on my work machine aswell.

Oli

• ###### 1. Re: Calculated field to change colour of Line Graph

Hi Oli

Fun problem

How about something like this? (made in superstore) (you can choose the colours!)

Cheers

Mark

• ###### 2. Re: Calculated field to change colour of Line Graph

Formula

IF SUM([Sales]) > LOOKUP(SUM([Sales]),-1) THEN 'UP' ELSE 'DOWN' END

• ###### 3. Re: Calculated field to change colour of Line Graph

Hi Mark,

I am getting an error in my calculated field, it says : 'Argument to SUM (an aggregate function) is already an aggregation, and cannot be further aggregated.'

This is probably becuase my mesure is an Aggregation. Is there anyway around this problem?

Oli

• ###### 4. Re: Calculated field to change colour of Line Graph

Hi Oli

I'm currently away from my desk.

I'm pinging myself as a reminder to reply when I'm able...

We need to look at the aggregation of the previous calculation

there is some interesting discussions here on sum, window_sum vs total

Mark Fraser