Simple KPI How to make % variance when +ve color change to Blue -ve RED ?

[paulyeo11]

Hi All

I copy the this KPI from QS example , i dont know how to make the % change to red color when % is +ve ? Now it display black color , i need to make it display Red color when -ve. Now alway black.

I have try use the below expression , i insert at color value :-

=if([Sales vs Target] <0% ,'#d01919','#545352')

But it does not work.

Paul

[danansell42]

Hi Paul

It doesn't seem to be evaluating [Sales vs Target].

if you was to replace this with the full expression then the colors will change.

e.g.

if(if(

(

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/1000/Rate)

/

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales_target/1000/Rate)

)

>1,1,

(

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/1000/Rate)

/

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales_target/1000/Rate)

)

) >0 ,'#d01919','#545352')

Thanks

Dan

[paulyeo11]

Hi Karunpreel

I already try your expression in QS , but the color of the text never change.

Enclosed my QS QVF file

I have created 2 KPI box , one is -ve % another one is +Ve % , but both display black.

Paul

[paulyeo11]

Hi Daniel

I have try to place your expression into Box 1 and 2 . Both display same red color. can you take a look of my qvf ?

[danansell42]

Hi Paul

For your test calculation you have added -1* 'calculation' to turn the number negative

Whereas in the colour expression you haven't added the same.

Therefore when it evaluates the expression the number is still positive and therefore red.

Thanks

Dan

[paulyeo11]

Hi Daniel

I have add *-1 to simulate.

if you look at below image you will notice that the 2 box , one is +ve another one is -ve , why the text color same color ?

Paul

[danansell42 · Accepted solution]

As mentioned above, you haven't included the *-1 in the colour expression to finish of the simulation.

The colour expression evaluates separately from your main expression and is therefore still calculating a positive number.

I've just tested it and works perfectly for me.

Example for colour expression:

if(-1*if(

(

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/1000/Rate)

/

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales_target/1000/Rate)

)

>1,1,

(

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/1000/Rate)

/

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales_target/1000/Rate)

)

) >0 ,'#d01919','#545352')

[paulyeo11]

Hi Daniel

I think I understand now. But then it is possible to make use of macro ? So that when I make change of the actual expression , it will not affect the value Color expression ?

Paul

Sent from my iPhone

[danansell42]

Hi Paul

Not really sure about macro's.

And it seems you can't use the master items as part of the color expression unfortunately.

Hopefully its been at least partially resolved and warrants a correct answer for now

Dan

[paulyeo11]

hi Daniel

thank you very much for your help.

PAul

[paulyeo11]

Hi Daniel

I have check with the simple KPI developer , how to make use of macro for his KPI , the reply as below :-

https://mail.google.com/mail/u/0/?tab=wm#inbox/15591447c2b0fbab

it is possible you can share with me what Mr Alex try to said ? As i cannot understand.

Paul