Color of text not changing in pivot table

[ashis]

Hi,

I have a pivot table and it has one calculated dimension. The expression is as follow:

=num(aggr(sum({<XXX=,Date={'$(vDate)'}>}YYYY),AAA, BBB,CCC,DDD),'#,##0;-#,##0')

I want negative value in red so in the text color of calculated dimension I used the following expression :

=if(sum({<xxx=,Date={'$(vDate)'}>}TOTAL<AAA, BBB,CCC,DDD>YYYY)<0,rgb(255,0,0),Black())

The above expression is working fine however I notice it is not working for few days only.

Please share your thought what could be the issue or should I need to change the code.

[Anil_Babu_Samineni]

Haha, What you mean the "Few days not working". 6 months working and 6 months not working in a year. Have you upgrade any s/w

[sushil353]

You can use the same expression for color as in the main expression:

if(num(aggr(sum({<XXX=,Date={'$(vDate)'}>}YYYY),AAA, BBB,CCC,DDD)>0,Black(),RGB(255,0,0))

HTH

Sushil

[ashis]

For certain dates like example 2017-11-20 , the text color of its value not changing .

Ya it is very weird behavior .

[ashis]

Hi Sushil,

Agg function does not work in text coloring . That's why I used the above code.

[Anil_Babu_Samineni]

Could be vDate having same value, Certainly it won't

[t_chetirbok]

Hi,

Aggr works in the Text Color block.

You have problem with something else

I just put the same expression

=if(num(aggr(sum({<XXX=,Date={'$(vDate)'}>}YYY),AAA, BBB,CCC,DDD),'#,##0;-#,##0')<0,lightred())

Can you provide an example of your chart?

[ashis]

Hi Tatsiana,

In pivot table I have few calculated dimensions and few expressions.

If I use aggr function to change the text color conditionally it works for few rows and for few rows it does not.

I read from issue in qlik community (do not have link handy) that aggr is not able to read row by row hence it does not render the proper text color to overcome this issue I used total qualifier .

it is working absolutely fine except few dates . I am not sure where to look.

if I change the expression as you suggest then it does not work for me for any dates.

[sushil353]

Hi Ashis,

Do you have any null values in the rows.. generally is you have null values in the rows of pivot table then this color option does not work.

To fix this in presentation tab replace null or invalids with 0.

HTH

Sushil

[ashis]

Hi Sushil,

Yes I do have null values in the pivot rows. From presentation tab replacing null value with 0 is not fixed the issue.

However if I uncheck show all values from the dimensions tab then I do not see the missing values and it also resolve the text color issue.

My requirement is show all values , I need to look how do I replace the null values with zero.

Thank you for pointing out this one.