topic Re: Pivot Help in QlikView

Pivot Help

arusanah — Fri, 01 Jul 2016 16:17:16 GMT

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

Re: Pivot Help

arusanah — Fri, 01 Jul 2016 16:41:04 GMT

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

so far not able to merge above expressions.

Re: Pivot Help

sunny_talwar — Fri, 01 Jul 2016 19:07:57 GMT

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;

Re: Pivot Help

sunny_talwar — Fri, 01 Jul 2016 19:19:09 GMT

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])

Re: Pivot Help

arusanah — Fri, 01 Jul 2016 20:16:30 GMT

Hey Sunny ,

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

Re: Pivot Help

sunny_talwar — Fri, 01 Jul 2016 20:26:11 GMT

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

Re: Pivot Help

arusanah — Fri, 01 Jul 2016 21:19:50 GMT

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

Thank you so very Much !!!

Re: Pivot Help

sunny_talwar — Fri, 01 Jul 2016 21:21:14 GMT

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

Best,

Sunny