Conditional show and colour in qliksense pivot table

[kumar_72]

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:

Thanks in Advance.

[GaryGiles · Accepted solution]

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)

[NitinK7]

I pivot table measure section write below measure

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

and 

Background color exp

if(Date=Today(),Component_Status)

[kumar_72]

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.

[kumar_72]

thanks @NitinK7 

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

[GaryGiles]

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.  

[kumar_72]

@GaryGiles 

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.

[GaryGiles]

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

[kumar_72]

@GaryGiles 

thanks a lot, I am getting the right result.