Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Conditional Formatting in Qliksense Table

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

1 Solution

Accepted Solutions
Highlighted
Contributor II
Contributor II

Re: Conditional Formatting in Qliksense Table

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

View solution in original post

8 Replies
Highlighted
Employee
Employee

Re: Conditional Formatting in Qliksense Table

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())

 

qliktom_1-1593440020899.png

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

qliktom_0-1593439789409.png

*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...

Highlighted
Contributor II
Contributor II

Re: Conditional Formatting in Qliksense Table

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

Highlighted
Employee
Employee

Re: Conditional Formatting in Qliksense Table

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())

 

 

Highlighted
Creator II
Creator II

Re: Conditional Formatting in Qliksense Table

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())

Highlighted
Contributor II
Contributor II

Re: Conditional Formatting in Qliksense Table

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

 

Highlighted
Creator II
Creator II

Re: Conditional Formatting in Qliksense Table

may be like below

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

Highlighted
Contributor II
Contributor II

Re: Conditional Formatting in Qliksense Table

Hi Nitin,

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

Appreciate your time and support.

Regards,

Richard

Highlighted
Contributor II
Contributor II

Re: Conditional Formatting in Qliksense Table

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

View solution in original post