Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
my guess is it shud be sum of current month count & Previous month count .
so far not able to merge above expressions.
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;
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])
Hey Sunny ,
its not letting me open ur QVW file . is it possible to for u to send me script.
Thank You Sunny . I knew you would be able to help me
Thank you so very Much !!!
Best,
Sunny