topic Re: Dimension background color problem in pivot ? bug ? in QlikView

Dimension background color problem in pivot ? bug ?

Anonymous — Tue, 24 May 2011 14:05:21 GMT

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.

Regards,

Damien

Ce message a été modifié par: dcoueron

Re: Dimension background color problem in pivot ? bug ?

Wed, 25 May 2011 08:47:14 GMT

=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

Dimension background color problem in pivot ? bug ?

Wed, 25 May 2011 08:54:41 GMT

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

))))))))))

Dimension background color problem in pivot ? bug ?

marcos — Wed, 25 May 2011 09:43:32 GMT

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.

Dimension background color problem in pivot ? bug ?

Anonymous — Wed, 25 May 2011 10:03:34 GMT

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.

Re: Dimension background color problem in pivot ? bug ?

Anonymous — Wed, 25 May 2011 12:28:55 GMT

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

Re: Dimension background color problem in pivot ? bug ?

Anonymous — Wed, 25 May 2011 14:06:33 GMT

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.

Re: Dimension background color problem in pivot ? bug ?

johnw — Wed, 25 May 2011 16:35:55 GMT

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.

Re: Dimension background color problem in pivot ? bug ?

johnw — Wed, 25 May 2011 16:54:40 GMT

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

Re: Dimension background color problem in pivot ? bug ?

Anonymous — Wed, 25 May 2011 17:16:10 GMT

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 !

Re: Dimension background color problem in pivot ? bug ?

johnw — Wed, 25 May 2011 17:30:31 GMT

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

Re: Dimension background color problem in pivot ? bug ?

Anonymous — Wed, 25 May 2011 17:37:51 GMT

Yes it works perfectly.

Thank you a lot !