Conditional Colors of KPI

[Anonymous]

Dear Qlik Community, 

I am still new to the software QlikSense and am seeking for suggestions.

I am now facing a difficult task. I am required to set up color function for a specific KPI, let's say Purchase Quote. Target values for each Business Unit (BU) as well as for the cooperation as a whole are defined.

Target Value

BU A 70%

BU B 70%

BU C 80%

Coorporation 75%

(There is a table called for target values for each BU. The target value for the cooperation as a whole is defined directly in set analysis. )

If a user choose a business unit, the actual quote will be compared with its coresponding target value.If none of BU is chosen (or if all BUs are chosen), the actual quote of the coorperation will be compared with its target value 75%. If the actual quote is greater then the target value, the KPI will be shown in green, vice versa. Here is the coding I used. 

=if (
count(distinct BU) = 1

  ,if  ((Sum(ItemA)/(Sum([Total Purchase]))>=[Target Value BU]
     ,'#46c646'
     ,'#f93f17')

,if (
count(BU) =4
   ,if  ((Sum(ItemA)/(Sum([Total Purchase]))>=0.75
    ,'#46c646'
    ,'#f93f17'
 )
))
 

The problem I am having is that the system only recognize the green part. Which means, the conditional color works if a user only chooses one BU, but once no BU is chosen, it fails. However, if I switch the two parts, the red part would work, but not the green part. 

My question is, is there any possible way to combine/correct the "if function" so that both parts work?

Thank you very much!

Claire 

[agigliotti]

you can use the below expression.

if( isnull(GetFieldSelections(BU)) or count(BU) = count( {1} BU),
your_business_logic_if_none_or_allvalues_selected,
your_business_logic_if_some_values_is_selected )

[paarthsanghavi]

Try using

if( GetSelectedCount(BU) =1

, Comparison against Actual quote

, Comparison against 75%

)

[Anonymous]

Hallo Paarthsanghavi, 

thanks for your suggestion. It seems I made the coding too complicated that the system fails to recognize it.  Now it works 🙂

Claire

[Anonymous]

Hi, Agigliotti, thanks for your help. I have tried it, but the color function does not worl. What does the coding say? Why do we use isnull? Just out of curiosity...I am not very familiar with isnull function.

Best,

Claire