7 Replies Latest reply: Jul 2, 2018 6:08 AM by Andrea Gigliotti

# Color Expression.

Hi All,

in this Pivot table i want to get color condition, that if average rate of present month is lower than average rate of previous month then the value for present month's average rate should highlight in red color

i used -

IF(before(column(2))>(column(2)), RGB(201, 17, 62), green()) (for color expression)

but its giving wrong output

>my expression for average rate is -

AVG({\$<market={'DOMESTIC'}, [date.Calendar.Year]={'2018'},im_descr-={'*SCRAP*'}, im_descr_limited_flag = {1} >}rate)

• ###### Re: Color Expression.

is this correct?

if(

Sum(

Aggr(AVG({\$<market={'DOMESTIC'},[date.Calendar.MonthRelNo]={'0'},[date.Calendar.Year]={'2018'},im_descr-={'*SCRAP*'}, im_descr_limited_flag = {1} >}rate),rate)

)

>

Sum(

Aggr(AVG({\$<market={'DOMESTIC'},[date.Calendar.MonthRelNo]={'1'},[date.Calendar.Year]={'2018'},im_descr-={'*SCRAP*'}, im_descr_limited_flag = {1} >}rate),rate)

)

,'RED', 'GREEN')

• ###### Re: Color Expression.

I think you should sort for date numerically asc first.

• ###### Re: Color Expression.

Hi,

thanks for the reply, can you please correct it? i didn't understand:(

regards

• ###### Re: Color Expression.

did you solve it?

• ###### Re: Color Expression.

no

• ###### Re: Color Expression.

maybe this:

=if(

Sum(

Aggr( AVG( {\$< market={'DOMESTIC'}, [date.Calendar.MonthRelNo]={'0'}, [date.Calendar.Year]={'2018'}, im_descr-={'*SCRAP*'}, im_descr_limited_flag = {1} >} rate ), rate )

)

>

Sum(

Aggr( AVG( { \$< market={'DOMESTIC'}, [date.Calendar.MonthRelNo]={'1'}, [date.Calendar.Year]={'2018'}, im_descr-={'*SCRAP*'}, im_descr_limited_flag = {1} >} rate ), rate )

)

, LightRed(), Green() )

and set Sorting by Month numerically ASC.

Otherwise simplest for color expression :

if( After( column(2) ) > column(2), RGB(201, 17, 62), green() )