topic Re: Expression dinamic bkacground color in QlikView

Expression dinamic bkacground color

juan_patrick — Fri, 16 Jan 2015 12:47:58 GMT

Hi, i have a pivot table with some dimensions and one expression; one of the dimensions is year-month.

I want to put a background color in the expressions using two colors, in the expression bakground color i put this:

=$(vColorGrillaAlternativo(ColumnNo()))

and the definition of the variable is:

SET vColorGrillaAlternativo

= if(Odd($1),rgb(250,240,230),rgb(250,235,215));

The problem is that, for a month, i have no data for the expression so it repeat the same colour for background because for qlikview exists that column but dont show because have no data... ¿Any suggestions? Thanks!!

Re: Expression dinamic bkacground color

Gysbert_Wassenaar — Fri, 16 Jan 2015 13:31:31 GMT

Can you post a document that demonstrates the problem?

Re: Expression dinamic bkacground color

juan_patrick — Fri, 16 Jan 2015 14:07:19 GMT

I upload an excell with the example:

You can see the dates (weeks), between the weeks 20/06/2014 and 04/07/2014 is missing the 27/06/2014 (its exists in the calendar, but there is no data for it in the expression)

So, if put the columnn number, qlikview returns:

20/06/2014 -> column 3

04/07/2014 -> column 5

Thats why qlikview repeat the bakcground color... the column 4 have no data and so it's hide

Thank you!

Re: Expression dinamic bkacground color

Gysbert_Wassenaar — Fri, 16 Jan 2015 14:14:09 GMT

Let me rephrase that: Can you post a qlikview document that demonstrates the problem?

Re: Expression dinamic bkacground color

juan_patrick — Fri, 16 Jan 2015 15:13:02 GMT

I uploaded an example!

Re: Expression dinamic bkacground color

Gysbert_Wassenaar — Fri, 16 Jan 2015 15:29:56 GMT

Change the expression to =sum({<total={'*'}>}total) to eliminate the days without total values.

Re: Expression dinamic bkacground color

juan_patrick — Fri, 16 Jan 2015 19:34:11 GMT

It didnt work.

I have a variable that contains:

AVG( {< $1 = {1}, DIM_GRUPO={'$2'} >} CANTTOTAL );

I added CANTTOTAL={'*'}

to the variable but still do the same.

Re: Expression dinamic bkacground color

Sun, 18 Jan 2015 14:18:03 GMT

Remove empty values at dimension level for [day]:

=If(Aggr(Sum([total]), [day]) <> 0, [day])

Re: Expression dinamic bkacground color

juan_patrick — Mon, 19 Jan 2015 14:46:08 GMT

It doesnt work

Re: Expression dinamic bkacground color

Mon, 19 Jan 2015 15:09:12 GMT

Is this not correct?

Re: Expression dinamic bkacground color

juan_patrick — Mon, 19 Jan 2015 15:24:22 GMT

No, I have a variable in the expression so i must write the same in the dimension and didnt work.

the variable is:

$(myVar(Param1,Param2))

and inside do:

AVG( {< $1 = {1}, Dg={'$2'} >} CANTTOTAL );

If i put this into the dimension :

=if( $(myVar(Param1,Param2)) <> 0, FINSEMANA)

FINSEMANA -> is the day that the data do a control cut.

the error:

Error in calculated dimension.

Re: Expression dinamic bkacground color

Mon, 19 Jan 2015 16:47:18 GMT

You forgot the "Aggr" function. Something like that:

=if( Aggr($(myVar(Param1,Param2)), FINSEMANA) <> 0, FINSEMANA)

Maybe it will need add more independed keys to "aggr" parameters, if they placed in the left of this dimension.

Re: Expression dinamic bkacground color

Gysbert_Wassenaar — Mon, 19 Jan 2015 17:08:59 GMT

The example you posted works with the modified expression. Your example does not contain a field CANNOT or a field DIM_GRUPO. If you need help with that you will have to post a qlikview document that accurately models your real document.