# App Development

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor III

## Conditional show and colour in qliksense pivot table

Hello Experts,

I have one requirement where i want to color the Date field based on Component_Status field for each component when Date is equal to today's date. Since todays date is 25 Feb so it should color the rows of  2/25/2021  for each component based on Component_Status field.

Also it should show when each Component is reaching Component_ECD(by marking the cell with 1).

Sample data

 Product Component Date Component_Status Component_ECD A X 2/23/2021 Green 2/26/2021 A X 2/24/2021 Green 2/26/2021 A X 2/25/2021 Green 2/26/2021 A X 2/26/2021 Green 2/26/2021 A Y 2/23/2021 Red 2/23/2021 A Y 2/24/2021 Red 2/23/2021 A Y 2/25/2021 Red 2/23/2021 A Y 2/26/2021 Red 2/23/2021 A Z 2/23/2021 Yellow 2/26/2021 A Z 2/24/2021 Yellow 2/26/2021 A Z 2/25/2021 Yellow 2/26/2021 A Z 2/26/2021 Yellow 2/26/2021

Expected Output:

Labels (1)
• ### Qlik Sense

1 Solution

Accepted Solutions
Specialist

In your pivot table, with Product and Component as your Row Dimensions and Date as your Column Dimension;

use the following as your measure:

if(sum(if(Date=Component_ECD,1,0))=0,'',sum(if(Date=Component_ECD,1,0)))

And, for your background color for the measure, use:

if(Date=Today(),Component_Status)

8 Replies
Specialist

In your pivot table, with Product and Component as your Row Dimensions and Date as your Column Dimension;

use the following as your measure:

if(sum(if(Date=Component_ECD,1,0))=0,'',sum(if(Date=Component_ECD,1,0)))

And, for your background color for the measure, use:

if(Date=Today(),Component_Status)

Specialist

I pivot table measure section write below measure

Sum(pick(Match(Date,Component_ECD),1))

and

Background color exp

if(Date=Today(),Component_Status)

Contributor III
Author

thanks  @GaryGiles

with above expression it is working fine, one thing which i forgot in the requirement is the color should also roll up in the Totals cell, each color has assigned with one priority(Green=3, Yellow=2, Red=1), Totals cell color will be minimum of the Component's  color present  i.e  in above case all 3 colors are present so Totals cell color should be Red (based on minimum priority Criteria), likewise for other colors combination.

Contributor III
Author

thanks @NitinK7

your expression is also working correct, could you please also help me with Color Roll up requirement.

Specialist

Unfortunately, coloring cannot be applied to the total line in a Qlik Sense pivot table.  At least, not that I am aware of.  I looked into it awhile back, but did not find a solution for applying conditional color to the total line.

Contributor III
Author

with Total line i meant the Product cell .Minimum of the Component color should roll up in Product Cell. Please find below image for your reference.

Specialist

Try this for you background color expression:

if(Date=Today(),
if(Dimensionality()=1,
pick(min(Pick(Match(Component_Status,'Red','Yellow','Green'),1,2,3)),'Red','Yellow','Green'),
Component_Status))

Contributor III
Author

thanks a lot, I am getting the right result.

Tags
Community Browser