Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

dcoueron
New Contributor III

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

Re: Dimension background color problem in pivot ? bug ?

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

11 Replies
Not applicable

Re: Dimension background color problem in pivot ? bug ?

=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

Dimension background color problem in pivot ? bug ?

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

Dimension background color problem in pivot ? bug ?

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.

dcoueron
New Contributor III

Dimension background color problem in pivot ? bug ?

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.

dcoueron
New Contributor III

Re: Dimension background color problem in pivot ? bug ?

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

dcoueron
New Contributor III

Re: Dimension background color problem in pivot ? bug ?

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.

MVP
MVP

Re: Dimension background color problem in pivot ? bug ?

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.

MVP
MVP

Re: Dimension background color problem in pivot ? bug ?

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

dcoueron
New Contributor III

Re: Dimension background color problem in pivot ? bug ?

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 !

Community Browser