Conditional Formatting in Qliksense Table

[richard24best]

Hi All,

Hope you are doing good.

Need your assistance on my below query -

I have a table which has names, Target and Measures for different users(Ramesh, Suresh, Ganesh)

Want to create a conditional formatting to change color in table based on each value vs Target for Location.

File attached for your reference.

Appreciate if could check and let me know please.

Thanks and Regards,

Richard

[QlikTom]

Assuming your data structure is exactly like your image (with no aggregations) each column in your table will need a unique Background Color Expression:

=if(Only(Ramesh)>=Only(Target),Green(),red())
=if(Only(Suresh)>=Only(Target),Green(),red())
=if(Only(Ganesh)>=Only(Target),Green(),red())

In the table below, I did not update the labels, instead leaving the expression that made the column as the label on each

*Only() returns a value if there is one and only one possible result from the aggregated data. For example, searching for the only product where the unit price =9 will return NULL if more than one product has a unit price of 9.

https://help.qlik.com/en-US/sense/latest/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/BasicAggreg...

**More on Coloring by Expression
https://help.qlik.com/en-US/sense/latest/Subsystems/Hub/Content/Sense_Hub/Colors/color-by-expression...

[richard24best]

Hello,

Thank you for your reply.

Is it possible to include Location as well in the condition as the target is similar to other columns as well?

I mean when location is A, Ramesh>=Target should be green, red. Appreciate your assistance.

Thanks again,

Richard

[QlikTom]

Of course, based on your image, I assumed each location had a single target. 

=if(Location='A' AND Only(Ramesh)>=Only(Target),Green(),red())

If the target is not a part of the data model, you can just type in the target as well.

=if(Location='A' AND Only(Ramesh)>=.8,Green(),red())

[NitinK7]

May be like below

=if(location='A' and Only(Ramesh)>=Only(Target),Green(),red())
=if(location='B' and Only(Suresh)>=Only(Target),Green(),red())
=if(location='C' and Only(Ganesh)>=Only(Target),Green(),red())

[richard24best]

Hello,

Apologies as I am getting error saying nested aggregation not allowed.

Values for Ramesh, Suresh, Ganesh are calculated as per

sum({$<User = {'Ramesh'}>}Accounts)/sum({$<User = {'Ramesh}>}PTP_Trades)

so I was using as per your advice like this, please correct me if iam wrong

=if(Location='A' AND Only(sum({$<User = {'Ramesh'}>}Accounts)/sum({$<User = {'Ramesh}>}PTP_Trades))>=Only(Target),Green(),red())

I should have attached excel before, apologies for the confusion.

Regards,

Richard

[NitinK7]

may be like below

=if(Location='A' AND sum({$<User = {'Ramesh'}>}Accounts)/sum({$<User = {'Ramesh}>}PTP_Trades)>=Only(Target),Green(),red())

[richard24best]

Hi Nitin,

Thanks for your reply, i am getting error saying ' error in expression'.

Appreciate your time and support.

Regards,

Richard

[richard24best · Accepted solution]

Hi All,

Thank you for time and support, I figured it out and it worked -

=if(sum({$<Location = {'A'}>}Accounts)/sum({$<Location = {'A'}>}Trades)>=Target,'green','red')

Regards,

Richard