Latest month traffic light

[vidhya91gct]

Hi All,

I want to show the latest month state with the following logic for traffic light.

G If all green, = traffic light is green

Y If no red, but any yellow, then traffic light is yellow

R If any red, traffic light is red

for Ex: latest month is Mar2018 and it is Y and G , so i want to show Y (yellow traffic light)

Please find the attached table.

GYR Category		Oct 2017	Nov 2017	Dec 2017	Jan 2018	Feb 2018	Mar 2018
AAA		Y - S	Y - S	Y - U	G - S	Y - S	Y-S
BBB		Y - S	Y - U	Y - D	R - U	Y - D
CCC		Y - S	G - S	G - S	G - S	G - S	G-S
DDD		G - S	G - S	G - S	G - S	G - S
FFFF		Y - S	Y - D	Y - D	R - D	Y - D	Y-S

[Qrishna · Accepted solution]

Wheres the file?

[krishna20]

Hi Vidhya,

you missed the attachment. Pls share it. Is there any scenario that you will get any G- Y or Y- R or G- R

[vidhya91gct]

I am getting same format excel file and loaded it.

Catagory   GYR Month    Status

AAA           Oct 2018        G - S

BBBB         Nov 2018       Y  - D

CCC            Dec 2018      R  - D

DDD             Jan 2018      G -D

EEE              Feb2018       Y - S

FFF                Mar 2018    R - D     it is source format.

I have changed the Pivot table and getting in the below format

GYR Category		Oct 2017	Nov 2017	Dec 2017	Jan 2018	Feb 2018	Mar 2018
AAA		Y - S	Y - S	Y - U	G - S	Y - S	Y-S
BBB		Y - S	Y - U	Y - D	R - U	Y - D
CCC		Y - S	G - S	G - S	G - S	G - S	G-S
DDD		G - S	G - S	G - S	G - S	G - S
FFFF		Y - S	Y - D	Y - D	R - D	Y - D	Y-S

I want to show the latest month state with the following logic for traffic light.

G If all green, = traffic light is green

Y If no red, but any yellow, then traffic light is yellow

R If any red, traffic light is red

for Ex: latest month is Mar2018 and it is Y and G , so i want to show Y (yellow traffic light)