topic Re: Conditional show and colour in qliksense pivot table in App Development

Conditional show and colour in qliksense pivot table

Arun_141 — Mon, 20 Dec 2021 21:02:46 GMT

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.

Re: Conditional show and colour in qliksense pivot table

GaryGiles — Thu, 25 Feb 2021 11:42:15 GMT

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)

Re: Conditional show and colour in qliksense pivot table

NitinK7 — Thu, 25 Feb 2021 13:18:10 GMT

I pivot table measure section write below measure

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

and

Background color exp

if(Date=Today(),Component_Status)

Re: Conditional show and colour in qliksense pivot table

Arun_141 — Thu, 25 Feb 2021 14:31:54 GMT

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.

Re: Conditional show and colour in qliksense pivot table

Arun_141 — Thu, 25 Feb 2021 14:45:21 GMT

thanks @NitinK7

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

Re: Conditional show and colour in qliksense pivot table

GaryGiles — Thu, 25 Feb 2021 15:13:24 GMT

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.

Re: Conditional show and colour in qliksense pivot table

Arun_141 — Thu, 25 Feb 2021 15:34:10 GMT

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

Re: Conditional show and colour in qliksense pivot table

GaryGiles — Thu, 25 Feb 2021 16:22:50 GMT

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

Re: Conditional show and colour in qliksense pivot table

Arun_141 — Thu, 25 Feb 2021 19:00:56 GMT

@GaryGiles

thanks a lot, I am getting the right result.