topic Re: color banding on multiple rows (using rank() ?) in App Development

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

Anonymous — Mon, 13 Nov 2017 17:47:06 GMT

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.

What I am after is this...

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

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

shraddha_g — Tue, 14 Nov 2017 05:21:41 GMT

Try,

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

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

Anonymous — Tue, 14 Nov 2017 15:14:49 GMT

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.

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

shraddha_g — Wed, 15 Nov 2017 04:42:01 GMT

PFA .

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

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

mdmukramali — Wed, 15 Nov 2017 05:40:57 GMT

Hi,

Can you try Something like this.

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

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

mdmukramali — Wed, 15 Nov 2017 05:49:15 GMT

Hi,

PFA.

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

Anonymous — Wed, 15 Nov 2017 16:22:03 GMT

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.

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

antoniotiman — Wed, 15 Nov 2017 16:32:38 GMT

May be like this

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

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

Anonymous — Wed, 15 Nov 2017 16:46:42 GMT

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

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

antoniotiman — Wed, 15 Nov 2017 17:16:55 GMT

I apologize, my misunderstanding.

Try this :

In Script

Load *,AutoNumber(Pt&Date) as Flag

and condition like this

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

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

Anonymous — Wed, 15 Nov 2017 17:22:39 GMT

This method works if the data displayed in the chart are the complete contents of the loaded table. However, the data in the table I want to display is the result of selections in the app and so this solution could also produce consecutive colored bands.

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

mdmukramali — Wed, 15 Nov 2017 17:26:12 GMT

Hi brianrmacdonald

have you seen my solution?

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

Anonymous — Wed, 15 Nov 2017 17:31:40 GMT

Yes, I looked at it. It depends on the assumption that consecutive dates in the chart are even/odd which is not a good assumption. It is entirely possible that the selections made by the user will include all even days or all odd dates or random occurrences of either.