How to add different colours to each row in pivot table?

Help and How-To

Hello,

I have a pivot table which includes the day in one column.

I want to give each row a different colour. Also, the entire row has to be coloured.

For example:

Does anyone have a script that can do this?

JennyW

Posted 9 months ago·Last reply 8 months ago

5 comments

DRay

·8 months ago

Hello JennyW,

I’m following up to see if the solution offered by harikm007 worked for you.

If so, please click the 'Accept as Solution' button on the appropriate post. That way others with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

JennyW

OP8 months ago

Hi DRay,

Thanks for your reply!

I have seen the posts, however I'm stuck at the value part. In the posts the value is a certain number/text. However in my case, the text of the whole cell changes. I want to colour it based on the day but the date always changes.

Is it possible to set the value as 'contains Monday' instead of '== Monday'? I tried the latter but the script cannot find the cell since it contains 'monday [date]'.

Hope the question is clear and thanks for your help in advance!

harikm007

·8 months ago

Hi JennyW ,

If I understand it correctly, you need to color the Pivot Table's rows based on the content of a date field. If a row contains 'Monday', a certain color should be applied, and if it contains 'Tuesday', a different color should be set.

If that's the case, please try the script provided below. This script will work if the date field is the first column in the table.

const dayColorMapping = {
	'sunday': '#9cbff7',
	'monday': '#ee9cf7',
	'tuesday': '#f79cba',
	'wednesday': '#9cebf7',
	'thursday': '#abf79c',
	'friday': '#f7f49c',
	'saturday': '#f79c9c'
}

const weekday = ["sunday","monday","tuesday","wednesday","thursday","friday","saturday"];

const dateColumnIndex = 0;

widget.transformPivot(
    {
    },
    function setCellBackground(metadata, cell) {
				
		if(metadata.rowIndex > 0){
			
			const date = new Date(metadata.rows[dateColumnIndex].member);
			const day = weekday[date.getDay()];
			
			if(day in dayColorMapping) {
				cell.style.backgroundColor = dayColorMapping[day];
			}
		}
    }
);

Result:

Let me know if that works!

- Hari

JennyW

OP8 months ago

Thanks a lot Hari, it works!

Sorry for the late reply, I didn't receive a notification that a new comment was added.

I just wanted to check; is it possible to make it work if the days are not in the first column?
Also, I tried to use this in the dashboard script instead of the widget script, but then it doesn't work. Is there a way to make this work in the dashboard script? Reason is that the dashboard has a lot of tables and I want to apply the colours in every table.

- Jenny

DRay

·9 months ago

Hello JennyW,

Thank you for reaching out. Here are several posts that should point you in the right direction.

Color the Row and Value fields of a Pivot Table | Sisense Community

Changing Background Color Of Rows In A Pivot Based On Condition

How to use a widget.transformPivot function to change background color of the entire row based on a value within that row | Sisense

Please let us know if you need any additional assistance.