change cell color based on value in previous cell

[nicolai_moller]

Hi

I'm trying to colorcode cells if the cell above changes. The table below consists of two events, where Event A is inserted on december 15 and changes (updates) State and/or Start 3 times. Everytime State or Start changes is should be colored and

Event	Changelog Date	Changelog	State	Start
A	15122013	Insert	1	-
A	01012014	Update	2	01012014
A	01012014	Update	2	04012014
A	10012014	Update	3	10012014
B	01122013	Insert	1	02122013
B	05122013	Update	4	02122013
B	17122013	Update	3	02122013

The rows with Insert in them should never be colored as they have no previous status.

This is what I'm trying to achieve.

Event	Date	Changelog	State	Start
A	15122013	Insert	1	-
A	01012014	Update	2	01012014
A	01012014	Update	2	04012014
A	10012014	Update	3	10012014
B	01122013	Insert	1	02122013
B	05122013	Update	4	02122013
B	17122013	Update	3	02122013

Do I use peek in this situation? Thanks

[#2]

Use Set Analysis to conditionally color the cells.

[Nicole-Smith · Accepted solution]

The attached should give you what you need.

I started by writing some load script to flag where there are State or Start changes:

Data:

LOAD Event, date#([Changelog Date],'DDMMYYYY') as [Changelog Date], Changelog, State, Start INLINE [

    Event, Changelog Date, Changelog, State, Start

    A, 15122013, Insert, 1, -

    A, 01012014, Update, 2, 01012014

    A, 01012014, Update, 2, 04012014

    A, 10012014, Update, 3, 10012014

    B, 01122013, Insert, 1, 02122013

    B, 05122013, Update, 4, 02122013

    B, 17122013, Update, 3, 02122013

];

LEFT JOIN (Data)

LOAD Event, [Changelog Date], Changelog, State, Start,

    if(Event = Previous(Event) and State <> Previous(State), 1, 0) as [State Change],

    if(Event = Previous(Event) and Start <> Previous(Start), 1, 0) as [Start Change]

RESIDENT Data

ORDER BY Event, [Changelog Date], Changelog, State, Start;

Then I used these fields in a straight table to determine what the background color should be:

[Clever_Anjos]

You don´t need peek in this situation.

You can use a straihght table and "Above" function to form a BackGround Color Expression