Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

agrimroquette
Contributor II

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)

Capture.PNG

7 Replies
agrimroquette
Contributor II

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')

agigliotti
Honored Contributor II

Re: Color Expression.

I think you should sort for date numerically asc first.

agigliotti
Honored Contributor II

Re: Color Expression.

your expression looks good.

agrimroquette
Contributor II

Re: Color Expression.

Hi,

thanks for the reply, can you please correct it? i didn't understandSmiley Sad

regards

agigliotti
Honored Contributor II

Re: Color Expression.

did you solve it?

agrimroquette
Contributor II

Re: Color Expression.

no

agigliotti
Honored Contributor II

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() )

Community Browser