topic Re: Text color in red for calculated dimension in QlikView

Text color in red for calculated dimension

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

Hi,

I have a calculated dimension like the following

aggr(sum({<xxx=,Date={"$(vVariable)"}>}YYYY),AAA,BBB))

I want to print negative value in Red. so in text color I put the below code

if( aggr(sum({<xxx=,Date={"$(vVariable)"}>}YYYY),AAA,BBB)))<0,RGB(255,0,0),black()

As a result I am getting some negative value as red and some negative value are not in red. This is very strange .

Even if I put NUM function before the aggr the result is same.

Any help much appreciated.

Re: Text color in red for calculated dimension

Anil_Babu_Samineni — Tue, 28 Nov 2017 06:47:18 GMT

Perhaps this?

if(Sum(Aggr(Sum({<xxx=,Date={"$(vVariable)"}>}YYYY),AAA,BBB))<0,RGB(255,0,0),Black())

Re: Text color in red for calculated dimension

ashis — Tue, 28 Nov 2017 06:50:50 GMT

Thank you Anil for your reply, I tried with Sum as you said , however getting same result.

For some negative value I am getting it red and for some negative value I am getting black.

Re: Text color in red for calculated dimension

devarasu07 — Tue, 28 Nov 2017 06:56:06 GMT

Hi,

use actual expression instead of variable. i too faced similar issue while applied text color in a dimension field value.

or share your variable expression, mock data, i can help you

Thanks,
Deva

Re: Text color in red for calculated dimension

ashis — Tue, 28 Nov 2017 07:02:06 GMT

Thank you Devarasu, my vVariable is the date that is coming from a selection. If I do not use variable to pass date in the expression , how would I do alternately.

Re: Text color in red for calculated dimension

devarasu07 — Tue, 28 Nov 2017 07:12:52 GMT

Hi,

Can you share your mock app.

also u can try like below,

if ( aggr(sum({<xxx=,Date={"$(=max(Date))"}>}YYYY),AAA,BBB)) <0 ,RGB(255,0,0),Black())

Re: Text color in red for calculated dimension

ashis — Tue, 28 Nov 2017 07:13:06 GMT

Even if I remove the variable from the expression , the result is same.

Re: Text color in red for calculated dimension

Anil_Babu_Samineni — Tue, 28 Nov 2017 07:13:07 GMT

Can you try this first

If(Sum({<xxx=,Date={"$(vVariable)"}>}YYYY) <0, 1,0)

And then check with below

if(Sum(Aggr(Sum({<xxx=,Date={"$(vVariable)"}>}YYYY),AAA,BBB))<0,1,0)

Can you show the image after this with Value in text box?

Re: Text color in red for calculated dimension

devarasu07 — Tue, 28 Nov 2017 07:14:49 GMT

Hi,

Can you prepare mock data set and expected output in excel form. it's easy for us to check and help u. tks

Re: Text color in red for calculated dimension

ashis — Tue, 28 Nov 2017 09:05:21 GMT

Hi Anil,

Both the expression in text box is giving me value 1 .

For some restriction I am not able to paste the image.

Re: Text color in red for calculated dimension

Anil_Babu_Samineni — Tue, 28 Nov 2017 09:07:13 GMT

That means, You have only -Ve values not the +ve values

Re: Text color in red for calculated dimension

jyothish8807 — Tue, 28 Nov 2017 09:16:50 GMT

Hi Ashis,

Are you trying this in a straight table ? If so then make it an expression:

Dimension 1: AAA

Dimension 2: BBB

Expression: aggr(sum({<xxx=,Date={"$(vVariable)"}>}YYYY),AAA,BBB))

for text color: if(Sum(Aggr(Sum({<xxx=,Date={"$(vVariable)"}>}YYYY),AAA,BBB))<0,RGB(255,0,0),Black())

Re: Text color in red for calculated dimension

ashis — Tue, 28 Nov 2017 09:24:59 GMT

No , I am using in a pivot table.

Re: Text color in red for calculated dimension

jyothish8807 — Tue, 28 Nov 2017 09:28:30 GMT

No problem. The same goes in case of pivot also.

can you please share what is your dimension and expressions ?

Br,

Re: Text color in red for calculated dimension

Anil_Babu_Samineni — Tue, 28 Nov 2017 09:30:35 GMT

And do you have any cyclic Group / Drill down??

Re: Text color in red for calculated dimension

ashis — Tue, 28 Nov 2017 11:19:51 GMT

Hi Jyothish,

Following are my calculated dimension and expression for text color.

dimension =aggr(sum({<xxx=,Date={"$(vVariable)"}>}YYYY),AAA,BBB))

text color=if( sum(aggr(sum({<xxx=,Date={"$(vVariable)"}>}YYYY),AAA,BBB))))<0,RGB(255,0,0),black()

Re: Text color in red for calculated dimension

ashis — Tue, 28 Nov 2017 11:37:25 GMT

Yes in this chart I have one drill down .

Re: Text color in red for calculated dimension

Anil_Babu_Samineni — Tue, 28 Nov 2017 11:38:51 GMT

I think this makes wrong?

Re: Text color in red for calculated dimension

jyothish8807 — Tue, 28 Nov 2017 12:29:49 GMT

Hi Ashis,

Try to convert this dimension into expression and use the same text color condition.

Br,

Re: Text color in red for calculated dimension

ashis — Wed, 29 Nov 2017 03:57:13 GMT

Hi Jyothish,

If I convert the dimension into expression and use the same text color condition , it is working fine.

However my need is to display as dimension.