Latest month Traffic Light in Chart

[vidhya91gct]

Hi All,

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)

[chinnuchinni]

how did you get the below format ? based on which field you want to display the lights ? the below table for march its showing value for AAA,CCC,FFFF only.

can elaborate your requirement more clearly ?

if it is possible can you attach the sample doc
? so that will easy to resolve the problem to us.

[vidhya91gct]

I want to show the traffic light based on status

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

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

In this table,

G-S - green   , Y-S - yellow ,R-D - Red

i want show the traffic light based on the latest month.

for ex : latest month is Mar 2018 , getting status 'Y-S' , 'G-S','Y-S' , set the the 3 Segment traffic lights.

in the Mar'2018 column i have Yellow and green, so i want to show yellow color traffic light.

Scenerio :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

[chinnuchinni]

can you post your app ?

[vidhya91gct]

Sorry i dont have App, I have attached the above excel file, it is the final format.

I have the file(GYR Catatgory -AAA,BBB),GYR Month - Oct 2017,Nov 2017,Dec 2017,Jan 2018, Feb 2018, Mar 2018)

(GYR Status - Y-S, G-S , Y-S)

Changed to cross table(GYR Month, GYR Status,1) and getting 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 3 segment traffic light(Red,yellow and Green).

This 3 segment traffic light is compared with last month data,

i have Yellow and green, want to show yellow color 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