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: 
Anonymous
Not applicable

Dimension background color problem in pivot ? bug ?

Hello every one,

With Qlikview 10 SR2, i'm trying to assign a different background color to a dimension depending on the values of the dimension but when the expression of the graph returns no value, its associated dimension cell remains white.

I've read some posts about this problem and it was said to uncheck "Suppress Zero-Value" and check "Populate missing values". I've done this and it doesn't change a thing in my case.

Can any one help me with this ?

The expression set in "background color" option of the dimension is as follows :

=If (Alpha = 'A', RGB(100, 150, 250),

If (Alpha = 'B', RGB(90, 160, 250),

If (Alpha = 'C', RGB(80, 170, 250),

If (Alpha = 'D', RGB(70, 180, 250),

If (Alpha = 'E', RGB(60, 190, 250),

If (Alpha = 'F', RGB(50, 200, 250),

If (Alpha = 'G', RGB(40, 210, 250),

If (Alpha = 'H', RGB(30, 220, 250),

If (Alpha = 'I', RGB(20, 230, 250),

If (Alpha = 'J', RGB(10, 240, 250),

))))))))))

I have also attached an example file which shows the problem.

The problem is highlighted in the following picture, Alpha = 'D' is not colored because the first expression value (Dim22 = 'a') is empty.

test.jpeg

Regards,

Damien

Ce message a été modifié par: dcoueron

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

I assumed you wanted to show all values because you explicitly checkmarked "show all values" in your pivot table.  If you don'twant to show all values, remove the "show all values" checkmark and remove the {1} I added when I noticed you had "show all values" checkmarked.

=If(only(total <Alpha> Alpha) = 'A', RGB(100, 150, 250),
If (only(total <Alpha> Alpha) = 'B', RGB(90, 160, 250),
If (only(total <Alpha> Alpha) = 'C', RGB(80, 170, 250),
If (only(total <Alpha> Alpha) = 'D', RGB(70, 180, 250),
If (only(total <Alpha> Alpha) = 'E', RGB(60, 190, 250),
If (only(total <Alpha> Alpha) = 'F', RGB(50, 200, 250),
If (only(total <Alpha> Alpha) = 'G', RGB(40, 210, 250),
If (only(total <Alpha> Alpha) = 'H', RGB(30, 220, 250),
If (only(total <Alpha> Alpha) = 'I', RGB(20, 230, 250),
If (only(total <Alpha> Alpha) = 'J', RGB(10, 240, 250),
))))))))))

View solution in original post

11 Replies
Not applicable
Author

=If (RowNo()= 1, RGB(100, 150, 250),
 If (RowNo()= 2, RGB(90, 160, 250),
 If (RowNo()= 3, RGB(80, 170, 250),
 If (RowNo()= 4, RGB(70, 180, 250),
 If (RowNo()= 5, RGB(60, 190, 250),
 If (RowNo()= 6, RGB(50, 200, 250),
 If (RowNo()= 7, RGB(40, 210, 250),
 If (RowNo()= 8, RGB(30, 220, 250),
 If (RowNo()= 9, RGB(20, 230, 250),
 If (RowNo()= 10, RGB(10, 240, 250),
))))))))))

With RowNo() the cell is colored even if the first value of Dim22 is empty. I'm not sure that's exactly what you need, but it works in this case

test_dc.png

Not applicable
Author

I changed the background color expression as below

=If (Alpha = 'A', RGB(100, 150, 250),

If (Alpha = 'B', RGB(90, 160, 250),

If (Alpha = 'C', RGB(80, 170, 250),

If (Alpha = 'D' or IsNull(Alpha), RGB(70, 180, 250),

If (Alpha = 'E', RGB(60, 190, 250),

If (Alpha = 'F', RGB(50, 200, 250),

If (Alpha = 'G', RGB(40, 210, 250),

If (Alpha = 'H', RGB(30, 220, 250),

If (Alpha = 'I', RGB(20, 230, 250),

If (Alpha = 'J', RGB(10, 240, 250),

))))))))))

marcos
Partner - Contributor III
Partner - Contributor III

i had that problem some time ago, and the support line told me that this case was a WAD (Work As Design). The only solution is, as aiklamha says, put a specific color for the value that fails, but this is a solution very dificult to mainteain if the dimension data often vary.

If anyone knows how to solve it, i also welcome the information.

Anonymous
Not applicable
Author

Thank you all for your answers.

In my case the "or IsNull(Alpha)" solution will not work as there is more than one value that fails (D and G for example).

And the solution with RowNo() will work until there is a selection made on the Alpha dimension. In this case, the rowno won't be the same any more.

Anonymous
Not applicable
Author

I think i found a solution which works even when selections are made on the dimension.

The Background color setting for the Alpha dimension is as follows :

=If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'A' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 1), RGB(100, 150, 250),

If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'B' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 2), RGB(90, 160, 250),

If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'C' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 3), RGB(80, 170, 250),

If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'D' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 4), RGB(70, 180, 250),

If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'E' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 5), RGB(60, 190, 250),

If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'F' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 6), RGB(50, 200, 250),

If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'G' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 7), RGB(40, 210, 250),

If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'H' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 8), RGB(30, 220, 250),

If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'I' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 9), RGB(20, 230, 250),

If (SubField(GetFieldSelections(Alpha, ',', 26), ',', RowNo()) = 'J' Or (GetAlternativeCount(Alpha) = 0 And RowNo() = 10), RGB(10, 240, 250),

))))))))))

A warning so : this solution won't work if the order of the dimension values in the pivot table changes. 

Please find attached an example application.

Regards,

Damien

Anonymous
Not applicable
Author

Unfortunately, this solution doesn't work if a selection is made on a field which have an impact on the number of possible values for dimension Alpha but no direct selection is made on Alpha.

johnw
Champion III
Champion III

This won't work in every case, but it works for this specific case because there are SOME values out there for Alpha = D.  Maybe it'll be good enough for your actual application.

=If(only({1} total <Alpha> Alpha) = 'A', RGB(100, 150, 250),

If (only({1} total <Alpha> Alpha) = 'B', RGB(90, 160, 250),

If (only({1} total <Alpha> Alpha) = 'C', RGB(80, 170, 250),

If (only({1} total <Alpha> Alpha) = 'D', RGB(70, 180, 250),

If (only({1} total <Alpha> Alpha) = 'E', RGB(60, 190, 250),

If (only({1} total <Alpha> Alpha) = 'F', RGB(50, 200, 250),

If (only({1} total <Alpha> Alpha) = 'G', RGB(40, 210, 250),

If (only({1} total <Alpha> Alpha) = 'H', RGB(30, 220, 250),

If (only({1} total <Alpha> Alpha) = 'I', RGB(20, 230, 250),

If (only({1} total <Alpha> Alpha) = 'J', RGB(10, 240, 250),

))))))))))

Edit:  Or not.  Just noticed that you have a "show all values" in there.  So anything for which there is no selected data at all won't get the color.

Edit:  Fixed with set analysis.

johnw
Champion III
Champion III

Here's another approach.  Use a data island for the dimension, and use sum(if()) to link it to your data.  It'll cause performance problems on large data sets of course, but works even if there is NO data that matches in the entire database, and you're showing all values so that it's still visible.

AlphaIsland:

LOAD text(fieldvalue('Alpha',recno())) as Alpha2

AUTOGENERATE fieldvaluecount('Alpha');

CONCATENATE (AlphaIsland) // to demonstrate coloring with no data

LOAD '[' as Alpha2

AUTOGENERATE 1;

dimension 1 = Alpha2
dimension 2 = dim22
expression  = sum(if(Alpha=Alpha2,Expression32))

=If(Alpha2 = 'A', RGB(100, 150, 250),
If (Alpha2 = 'B', RGB(90, 160, 250),
If (Alpha2 = 'C', RGB(80, 170, 250),
If (Alpha2 = 'D', RGB(70, 180, 250),
If (Alpha2 = 'E', RGB(60, 190, 250),
If (Alpha2 = 'F', RGB(50, 200, 250),
If (Alpha2 = 'G', RGB(40, 210, 250),
If (Alpha2 = 'H', RGB(30, 220, 250),
If (Alpha2 = 'I', RGB(20, 230, 250),
If (Alpha2 = 'J', RGB(10, 240, 250),
if (Alpha2 = '[', lightgray() // to demonstrate coloring with no data
)))))))))))

Anonymous
Not applicable
Author

Thank you John for your answers.

I tried your first solution because our dataset is quite big and i don't want to use a data island if i don't have to.

I also had a problem with your first solution because no matter what selection i make on the Alpha dimension, every value is always shown in the pivot table.

In the mean time i came up with an other formula which does what i need (but i think it could be more resources consuming) :

IF(only({$ <Alpha={'A'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(100, 150, 250),

IF(only({$ <Alpha={'B'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(90, 160, 250),

IF(only({$ <Alpha={'C'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(80, 170, 250),

IF(only({$ <Alpha={'D'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(70, 180, 250),

IF(only({$ <Alpha={'E'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(60, 190, 250),

IF(only({$ <Alpha={'F'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(50, 200, 250),

IF(only({$ <Alpha={'G'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(40, 210, 250),

IF(only({$ <Alpha={'H'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(30, 220, 250),

IF(only({$ <Alpha={'I'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(20, 230, 250),

IF(only({$ <Alpha={'J'}>} TOTAL aggr(rank(TOTAL 26-ord(Alpha)), Alpha)) = RowNo(TOTAL), RGB(10, 240, 250)

))))))))))

Note : I doesn't work if the order of the pivot table is not the dimension order but it's okay for me.

Thanks every one !