topic Re: Color of text not changing in pivot table in QlikView

Color of text not changing in pivot table

ashis — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Color of text not changing in pivot table

Anil_Babu_Samineni — Tue, 19 Dec 2017 09:12:09 GMT

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

Re: Color of text not changing in pivot table

sushil353 — Tue, 19 Dec 2017 09:13:39 GMT

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

Re: Color of text not changing in pivot table

ashis — Tue, 19 Dec 2017 09:15:44 GMT

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

Ya it is very weird behavior .

Re: Color of text not changing in pivot table

ashis — Tue, 19 Dec 2017 09:16:42 GMT

Hi Sushil,

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

Re: Color of text not changing in pivot table

Anil_Babu_Samineni — Tue, 19 Dec 2017 09:19:04 GMT

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

Re: Color of text not changing in pivot table

t_chetirbok — Tue, 19 Dec 2017 09:49:25 GMT

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?

Re: Color of text not changing in pivot table

ashis — Tue, 19 Dec 2017 10:48:43 GMT

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.

Re: Color of text not changing in pivot table

sushil353 — Tue, 19 Dec 2017 11:21:11 GMT

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

Re: Color of text not changing in pivot table

ashis — Wed, 20 Dec 2017 05:40:41 GMT

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.

Re: Color of text not changing in pivot table

sushil353 — Wed, 20 Dec 2017 05:44:25 GMT

You can use alt() function to replace the null values.

Try: Alt(yourexpression,0)

HTH

Sushil

Re: Color of text not changing in pivot table

ashis — Wed, 20 Dec 2017 08:19:54 GMT

Hi Sushil,

Yes I already used Alt function in my expression , but did not work.

I guess we need to do it from database level.