Expressions to give background color to a Pivot Chart

[sqlpuzzle]

I have a sample data set.

The scenario is as below,

There are Groups which contains Teams and Teams participate in Programs.

Each Program has

•  #Task (Number of Tasks) 
• #Completed (Number of Tasks Completed)

When #Completed = #Tasks I give the metric green color else red.

When a Team gets green for all the programs it is involved in then the Team is given a green background else red.

When all the Team in a particular group gets green then the group gets green background else red.

Sample is attached.

Group>G1 should be red.

Team>A should be red

 How can I do this?

 

[rubenmarin]

Hi @sqlpuzzle, nulls() are red or those shouldn't be counted.

If nulls doen't count this can work:

Team =if(Min(TOTAL <Group, Team> Aggr(If(#Task = #Completed,1,0),Team,Program))=1,RGB(91,212,193),RGB(241,177,181))

Group: =if(Min(TOTAL <Group> Aggr(If(#Task = #Completed,1,0),Group,Team,Program))=1,RGB(91,212,193),RGB(241,177,181))

In expression you can use =If(Count(#Task),if(#Task = #Completed,RGB(91,212,193),RGB(241,177,181))) to not give background to null values

[sqlpuzzle]

Hi @rubenmarin ,

Thanks for the reply,

In this case Nulls should be treated as 0.

I tried your expressions.

The colors for Teams are not as I need.

Example

Team A has not completed the Program P2, so Team A should not get a green.

Similarly, Team D has not completed P1 so Team D should also not get green.

 

[rubenmarin · Accepted solution]

Hi @sqlpuzzle, as I said, that would work if nulls doesn't count, if they have to count as zero it will be better to fill data on script the create that 0, you can also have a field with 0 and 1 to do calculations and another that keeps nulls values to show the nulls.

To create zeros you can use something like:

// Check existintg combinations
chkKey:
LOAD Distinct Hash256(Group,Team,Program) as chkKey
Resident DataTable;

// Create all combinations
tmpCartesian:
LOAD Distinct
  Group, Team
Resident DataTable;

Outer Join (tmpCartesian)
LOAD Distinct
  Program, #Task
Resident DataTable;

// Add the combinations that doesn't exists with zeros
Concatenate (DataTable)
LOAD 
  Group, Team, Program, #Task,
  0 as #Completed
Resident tmpCartesian
Where not Exists('chkKey', Hash256(Group,Team,Program));

DROP Table chkKey;
DROP Table tmpCartesian;

[sqlpuzzle]

Thanks @rubenmarin .

I did end up transforming data to fill the null even though I did it outside QlikView.  Then I made use of your expressions.