Dynamic background color for dimension in pivot table with horizontal dimension?

[#1]

Hi,

we have a strange problem:

There  is a pivot table for which we want to define dynamic background colors for one of the dimensions depending of a calculation.

It works fine when all dimensions of the pivot table are just next to each other, going vertical.

But when we move the last dimension to go horizontal (to the right), it doesn't work properly. as soon as there is a missing value (e.g. because for a line there is no value in one of the dimensions which goes to the right) the expression for the color doesn't deliver any result.

For a better understanding i attached an example:

In the first object everything is fine.

In the second and third object the cell for Dim1 = B and Dim1 = C are not colored as they should. This happens because in these lines Dim2 starts with missing values (a and b show the -, so missing).

In the third chart the cells for B and C are gray, so the expressions seems to deliver NULL.

Do you know any trick how to surround or ignore the missing values and achieve the same coloring as in the very first chart?

Thanks a lot!

Regards,

Martin

[#2]

Hi,

a little correction: i wrote that in the third chart the expressions seems to deliver NULL because the cell is gray... but I misread my expression... actually it is gray because it doesn't deliver anything at all.... null would be yellow, not gray.

Looking forward to your tips. 🙂

Regards,

Martin

[cfz]

Hi Martin

The expression defining the color is an attribute expression, and it will only be calculated if the main expression is calculated. Coloring will not be applied if the first column result is missing or null values. This is a limitation in QlikView. If you can use a Straight Table instead of a Pivot Table you shouldn't experience the problem. If you must use a Pivot Table you can add subtotals on top to as a workaround.

I hope this help.

Kind regards

Carlos

[nizamsha]

PFA I Dont Know excatly wht u want@

[#5]

Hi Carlos,

thanks for your answer. unfortunately it needs to be a pivot table and since the cells itself need to be colored the use of subtotals wouldn't help. or do you think there is a chance to solve this by subtotals? could you perhaps modify my example in that way?

Thanks,

Martin

[#6]

hi Nizam,

thanks for your answer and example. Unfortunately i don't need the headers of the horizontal dimension (Dim2) to be colored but the cells of the vertical dimension (Dim1).

Regards,

Martin

[cfz]

Hi Martin,

The use of the subtotals solves the problem in the sense that now the cells are colored, but you will need to adjust the expression calculating the colors (since you are getting the wrong color sometimes). Look at your document modified that I have attached

Kind regards

Carlos

[arjunkrishnan]

Hello Frd ...

Use This Expression In Dimension Field ->Dim1->Background Color

=if(count(Dim3) >= 350 and RowNo(), LightCyan(),

    if(count(Dim3) < 350 or count(Dim3)>=330 and RowNo(), LightRed(),

        if(isnull(count(Dim3) and RowNo()), yellow(),

            LightGray()

        )

    )

)

It Well Work Fine