topic Re: Alternative marking of group of rows? in QlikView

Alternative marking of group of rows?

Vidit — Fri, 10 May 2013 07:43:34 GMT

Hi I have the following pivot table.

In which i want to differentiate each group of rows, may be by changing the background color of each set of rows alternatively.

Project Number	Project Name	Year - Month	Res	Bud	Sch	Qua
E-2012-007	A	2012-08	1	1	1	1
		2012-09	1	1	1	1
		2012-10	1	1	1	1
		2012-11	1	1	1	1
		2012-12	1	1	1	1
		2013-01	1	1	1	1
		2013-02	3	3	3	2
		2013-03	3	3	3	2
		2013-04	3	3	3	2
E-N-2013-002	B	2012-10	1	1	1	1
		2012-11	1	1	1	1
		2012-12	1	1	1	1
		2013-01	1	1	1	1
		2013-02	1	1	1	1
		2013-03	1	1	1	1
		2013-04	1	1	1	1
G-2012-015	C	2012-08	2	2	2	3
		2012-09	2	2	3	3
		2012-10	2	2	3	2
		2012-11	2	2	2	2
		2012-12	2	2	2	2
		2013-01	2	3	2	2
		2013-02	2	3	2	2
		2013-03	2	2	2	2

Desired O/p:

Can Any one suggest a solution?

Leo

Re: Alternative marking of group of rows?

alexandros17 — Fri, 10 May 2013 07:50:35 GMT

In the dimension backgroung property set

if(odd(RowNo()),RGB(255,0,0), rgb(0,0,255))

hope it helps

Re: Alternative marking of group of rows?

Gysbert_Wassenaar — Fri, 10 May 2013 07:57:53 GMT

Maybe something like this as expression for the background color for the dimensions and expressions:

if(even(FieldIndex('Project Number',[Project Number])),LightGray(200),Yellow(100))

Change Project Number with the name of your Project Number dimension.

Re: Alternative marking of group of rows?

Vidit — Fri, 10 May 2013 08:03:06 GMT

Hi Gysbert,

I tried with the following expression, but it doesnt give the desired result,

if([Project Number] = Above([Project Number]), RGB(100,100,100), RGB(100,0,0))

Re: Alternative marking of group of rows?

Vidit — Fri, 10 May 2013 08:12:01 GMT

The RowNo() returns the number of the row within a gruop of rows, but as soon as the group changes it resets to 1 again. Hence The above can be used if we want alternate coloring of rows in general, but for alternate coloring of grouped rows its not useful.

Re: Alternative marking of group of rows?

calvindk — Fri, 10 May 2013 08:35:13 GMT

Here you go:

if(odd(Aggr(Nodistinct RowNo(), [Project Name])), RGB(100,100,100), RGB(100,0,0))

Re: Alternative marking of group of rows?

Vidit — Fri, 10 May 2013 08:46:27 GMT

I used the expression in Background color of 'Sch' but its not working.

Re: Alternative marking of group of rows?

alexandros17 — Fri, 10 May 2013 08:48:34 GMT

Have you tried my expression ... it works ....

=if(odd(RowNo()),RGB(255,0,0), rgb(0,0,255))

Alexandros17

Re: Alternative marking of group of rows?

Vidit — Fri, 10 May 2013 08:53:55 GMT

It works for the alternate coloring of rows, but if you see the desired output, i want all the rows with same 'project Number' to be of same color, i.e. the color should change for groups of rows alternativly. please refer to the image for better understanding,

can you please share the sample if its working for you?

Thanks

Leo

Re: Alternative marking of group of rows?

calvindk — Fri, 10 May 2013 08:56:57 GMT

See attached.

If that is not what you want, please upload alternative example.

Re: Alternative marking of group of rows?

alexandros17 — Fri, 10 May 2013 09:05:47 GMT

Here it is ...

Re: Alternative marking of group of rows?

flipside — Fri, 10 May 2013 09:56:09 GMT

This wouldn't work for me, either, but it may be down to the limiting of dimensions through set analysis in the expressions, so I've come up with another solution (seems to work 99% of the time, anyhow) using the following idea (I'm using some user log data, but it should be simple to amend to suit your data) ...

Create a variable to concatenate your distinct dimension values, here I also add in my set analysis from the expression to limit the list as the Pivot Table will do ...

=concat({<[Session Start] = {">=$(DocDatesFrom)<=$(DocDatesTo)"}>} total distinct [Identifying user],',',[Identifying user])

... then use this expression to split the string at the relevant place and count the preceding commas (or whatever delimiter you are using) ...

=if(even(substringcount(subfield(vUsers,[Identifying user],1),',')),LightGray(200),Yellow(100))

I'm guessing the sort order of the Pivot table will be important too.

flipside

Re: Alternative marking of group of rows?

Vidit — Mon, 13 May 2013 07:36:59 GMT

Hi,

Thanx for the sample. this works for most of the cases, but if i have to apply any filters on the table than the alternate colring is not working properly. Refer to the image attached.

As you can see the A & C are both in red. But that should happen.

Any solution for this?

Leo

Re: Alternative marking of group of rows?

Gysbert_Wassenaar — Mon, 13 May 2013 07:56:29 GMT

See attached qvw.

Re: Re: Alternative marking of group of rows?

Mon, 07 Apr 2014 11:09:57 GMT

Hi Gysbert,

I also have the same requirement discussed in this post and tried to implement the way you did in the sample application comm81789.qvw .

Unfortunately it is not working for me properly. I have attached my sample application .Please help me where i am going wrong

Many thanks for your help and time.

Regards

Murali Krishna