Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kumar_72
Contributor III
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

ProductComponentDateComponent_StatusComponent_ECD
AX2/23/2021Green2/26/2021
AX2/24/2021Green2/26/2021
AX2/25/2021Green2/26/2021
AX2/26/2021Green2/26/2021
AY2/23/2021Red2/23/2021
AY2/24/2021Red2/23/2021
AY2/25/2021Red2/23/2021
AY2/26/2021Red2/23/2021
AZ2/23/2021Yellow2/26/2021
AZ2/24/2021Yellow2/26/2021
AZ2/25/2021Yellow2/26/2021
AZ2/26/2021Yellow2/26/2021


Expected Output:

Output.JPG

Thanks in Advance.

Labels (1)
1 Solution

Accepted Solutions
GaryGiles
Specialist
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)

View solution in original post

8 Replies
GaryGiles
Specialist
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)

View solution in original post

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

 

 

NitinK7_0-1614259035805.png

 

kumar_72
Contributor III
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.

 

4.JPG

 

 

kumar_72
Contributor III
Contributor III
Author

thanks @NitinK7 

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

GaryGiles
Specialist
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.  

kumar_72
Contributor III
Contributor III
Author

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

 

5.JPG

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

kumar_72
Contributor III
Contributor III
Author

@GaryGiles 

thanks a lot, I am getting the right result.