Pivot Help

[arusanah]

    Data Set

Person ID	MONTH	YEAR	Plan GRP	New Enrollment	Enrollment	Disenrollment
11111	January	2016	A	Y	Y	N
11111	February	2016	A	N	Y	N
11111	March	2016	C	N	Y	N
11111	April	2016	C	N	Y	N
11111	May	2016	C	N	Y	Y
11112	May	2016	A	Y	Y	N
11112	June	2016	A	N	Y	N
11112	July	2016	A	N	Y	Y
11113	June	2016	B	Y	Y	N
11113	July	2016	B	N	Y	N
11114	January	2016	C	Y	Y	N
11114	February	2016	C	N	Y	N
11114	March	2016	D	N	Y	N
11114	April	2016	C	N	Y	N
11114	May	2016	C	N	Y	Y
11115	January	2016	D	Y	Y	N
11115	February	2016	D	N	Y	N
11115	March	2016	D	N	Y	N
11115	April	2016	B	N	Y	N
11115	May	2016	B	N	Y	N
11115	June	2016	C	N	Y	N
11115	July	2016	D	N	Y	N
11116	January	2016	D	Y	Y	N
11116	February	2016	D	N	Y	N
11116	March	2016	D	N	Y	N
11116	April	2016	D	N	Y	Y
11117	January	2016	A	Y	Y	N
11117	February	2016	A	N	Y	N
11117	March	2016	A	N	Y	N
11117	April	2016	C	N	Y	N
11117	May	2016	C	N	Y	Y

		Desired Out Put
		Most Recent Grp
		A	B	C	D
Previous Grp	A	1	1	1	1
	B	1	1	1	1
	C	1	1	1	1
	D	1	1	1	1

My Requirement is to Generate a Pivot report in the above mentioned Format. This report is usually generated  Monthly. So when report is generated Person is counted in previous grp as well as new grp. For example in month  of JAN person 11111 was in plan A . He was in same plan in month Feb . but in March he moved from Plan A to plan C . so in above mentioned report he will be counted in plan A as well as plan C.   !!!

I'm facing difficulty how can I add current month value & Pervious month value in same cell

I am using following expression in QVW  at this point . how can I modify it to get above output.

Count({$<[Plan GRP]={'A','B','C','D'}>} distinct(([Person ID])))  ----- Current month Expression

Count({$<[Plan GRP]={'A','B','C','D'}, Month=(=month(addmonths(max(MonthYear)-1))>} distinct(([Person ID])))  ----- Last month Expression

Pls Help

[arusanah]

my guess is it shud be sum of current month count  & Previous month count .

so  far not able to merge above expressions.

[sunny_talwar]

Is this what you want?

Script Changes:

Test1:

LOAD [Person ID],

  MONTH,

  YEAR,

  Date(MonthStart(Date#(MONTH & '-' & YEAR, 'MMM-YYYY')), 'MMM-YYYY') as MonthYear,

  [Plan GRP],

  [New Enrollment],

  Enrollment,

  Disenrollment

FROM

[Test1.xlsx]

(ooxml, embedded labels, table is Sheet3);

FinalTest:

LOAD *,

  If([Person ID] = Previous([Person ID]), Previous([Plan GRP])) as [prev Plan Grp]

Resident Test1

Order By [Person ID], MonthYear;

DROP Table Test1;

[sunny_talwar]

Or may be this:

Expression difference:

1st response: Count({$<[Plan GRP]={'A','B','C','D'}>}[Person ID])

2nd response (this post): Count(DISTINCT{$<[Plan GRP]={'A','B','C','D'}>}[Person ID])

[arusanah]

Hey Sunny ,

its not letting me open ur QVW file . is it possible to for u to send  me script.

[sunny_talwar]

I did put the script with my 1st response. Did you miss the 1st response by any chance?

[arusanah]

Thank You Sunny . I knew you would be able to help me

Thank you so very Much !!!

[sunny_talwar]

.... I was not too confident as to what you needed... But I am glad I was able to help.

Best,

Sunny