Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

brianrmacdonald
Contributor

color banding on multiple rows (using rank() ?)

This seems like this should be easy but I cannot get this to work.  I want to alternate colors for rows of a table but the rows will be in groups of variable size.  I thought that I could use the rank function to denote the groups and then flag the rank as even or odd to control the color banding, but this is not working for me for some reason.  Here is some sample data:

Load * Inline [

Pt, Date, Product, Order

123, 1/1/2017, Apples, Order1

123, 1/1/2017, Pears, Order1

123, 1/1/2017, Oranges, Order2

123, 2/1/2017, Apples, Order3

123, 2/1/2017, Oranges, Order4

123, 2/1/2017, Bananas, Order5

123, 3/3/2017, Bananas, Order6

123, 3/3/2017, Pears, Order7

123, 3/3/2017, Apples, Order8

];

Here is what I am getting, The column Pt is first in the sort order...I know the rank function depends on sort order.

2017-11-13_11-42-22.jpg

What I am after is this...

2017-11-13_11-42-22_2.jpg

Thanks in advance for any help.  Feel free to suggest an easier way to do this.

12 Replies
shraddha_g
Honored Contributor III

Re: color banding on multiple rows (using rank() ?)

Try,

if(mod(num#(Date(Date,'D')),2) = 0,red(),green())

brianrmacdonald
Contributor

Re: color banding on multiple rows (using rank() ?)

Its an interesting solution, but in the example data I provided the 'D' for each date is an odd number so this formula colors the entire chart green. 

shraddha_g
Honored Contributor III

Re: color banding on multiple rows (using rank() ?)

PFA .

Used Expression is: if(mod(num#(Date(Date#(Date,'D/M/YYYY'),'D')),2)=0,White(),green())

mdmukramali
Valued Contributor II

Re: color banding on multiple rows (using rank() ?)

Hi,

Can you try Something like this.

=if(Even(Day(Date))=0,Red(),Green())

mdmukramali
Valued Contributor II

Re: color banding on multiple rows (using rank() ?)

Hi,

PFA.

brianrmacdonald
Contributor

Re: color banding on multiple rows (using rank() ?)

All of these solutions depend on the assumption that the dates are consecutive in some way, either consecutive days or months, etc.  This assumption is not a good one for my data.  If I change the sample data to this:

Load * Inline [

Pt, Date, Product, Order

123, 1/1/2017, Apples, Order1

123, 1/1/2017, Pears, Order1

123, 1/1/2017, Oranges, Order2

123, 2/1/2017, Apples, Order3

123, 2/1/2017, Oranges, Order4

123, 2/1/2017, Bananas, Order5

123, 4/3/2017, Bananas, Order6

123, 4/3/2017, Pears, Order7

123, 4/3/2017, Apples, Order8

];

This solution does not produce the desired results.

antoniotiman
Honored Contributor III

Re: color banding on multiple rows (using rank() ?)

May be like this

If(Even(RowNo(TOTAL)),Red(),Green())

brianrmacdonald
Contributor

Re: color banding on multiple rows (using rank() ?)

Produces this result....not what I am after.

2017-11-15_10-45-16.jpg

antoniotiman
Honored Contributor III

Re: color banding on multiple rows (using rank() ?)

I apologize, my misunderstanding.

Try this :

In Script

Load *,AutoNumber(Pt&Date) as Flag

and condition like this

If(Even(Flag),Red(),Green())

Community Browser