Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sqlpuzzle
Contributor III
Contributor III

Expressions to give background color to a Pivot Chart

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.

ColorSampleData.JPG

ColorSamplePivotChart.JPG

Group>G1 should be red.

Team>A should be red

 

 How can I do this?

1 Solution

Accepted Solutions
rubenmarin

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;

View solution in original post

4 Replies
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
Contributor III
Contributor III
Author

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.

IncorrectColor.JPG

 

rubenmarin

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
Contributor III
Contributor III
Author

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.