RAG for Quarter over Quarter Comparison

[priyankapassi]

Hi All,

I want if total count of primary key is less than total count of Primary key of Previous quarter then mark that row with Green  else red.

suppose, if

Date	Quarter	Primary Key
01-01-2016	Q1 16	abc
02-01-2016	Q1 16	abc
03-01-2016	Q1 16	abc
04-01-2016	Q2 16	pqr2
05-01-2016	Q2 16	pqr3
06-01-2016	Q2 16	pqr4
26-04-2016	Q2 16	pqr5
27-04-2016	Q2 16	pqr6
28-04-2016	Q2 16	pqr7
29-04-2016	Q2 16	xyz
30-04-2016	Q2 16	xyz
01-05-2016	Q2 16	xyz
29-10-2016	Q4 16	xyz
30-10-2016	Q4 16	xyz
31-10-2016	Q4 16	mno
01-11-2016	Q4 16	mno
02-11-2016	Q4 16	mno

should return

01-01-2016	Q1 16	abc	Green
02-01-2016	Q1 16	abc	Green
03-01-2016	Q1 16	abc	Green
04-01-2016	Q2 16	pqr2	red
05-01-2016	Q2 16	pqr3	red
06-01-2016	Q2 16	pqr4	red
26-04-2016	Q2 16	pqr5	red
27-04-2016	Q2 16	pqr6	red
28-04-2016	Q2 16	pqr7	red
29-04-2016	Q2 16	xyz	red
30-04-2016	Q2 16	xyz	red
01-05-2016	Q2 16	xyz	red
29-10-2016	Q4 16	xyz	Green
30-10-2016	Q4 16	xyz	Green
31-10-2016	Q4 16	mno	Green
01-11-2016	Q4 16	mno	Green
02-11-2016	Q4 16	mno	Green

Thanks in Advance.

[Kushal_Chawda]

Data:

LOAD Date,

     Quarter,

     [Primary Key]

FROM

[Data1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Count:

LOAD Quarter,

     Count([Primary Key]) as Countkey

Resident Data

Group by Quarter

Order by Quarter;

Left Join(Data)

LOAD *,

     if(RowNo()=1 or Countkey<Previous(Countkey),'Green','Red') as Staus

Resident Count;

DROP Table Count;