topic Re: Row Coloring for Missing Values in QlikView

Row Coloring for Missing Values

Sat, 18 Oct 2014 19:10:44 GMT

Hi,

As you can see in the attached app, each row of the table will take a color based on this expression in its background property:

if(even(aggr(NODISTINCT rowno(), PAIR)),rgb(210,210,210),white())

which is to highlight each pair of rows with alternating white and gray based on PAIR values.

This works fine until there is a missing value in one of the expressions in the table. For example, Customer F, PAIR 2, subcategory 4 has no imported data for Measure 1. So qlikview is not colorizing the subcategory 4 row with gray (as measure does not exist). However, this row has data for Measure 2.

So does anyone know of a trick to make it paint that row gray as it should.

Re: Row Coloring for Missing Values

kuba_michalik — Sat, 18 Oct 2014 21:06:12 GMT

If the PAIR order is crucial and it's not just about simple banding (for example, you make selections for subcategory so only one row per some PAIRs is visible), then use this:

If(Even(Only(TOTAL<PAIR> Aggr(NODISTINCT RowNo(), PAIR))),RGB(210,210,210),White())

as the color expression.

I find it a rather unusual use of Aggr. Thanks for the learning opportunity!

Re: Row Coloring for Missing Values

Sun, 19 Oct 2014 00:13:49 GMT

Thank you.