What Dimension do I use?

[dhborchardt]

I have tried a few different dimensions to get what I am after but each has a problem with it.
1: If I use the month for the dimension it will separate by months but not years.
2: If I use the year, it will separate the years but not the months.
3: If I use the Month/Year combination with the following calculation
Date(makedate(year(AdmitDate),month(AdmitDate)),'MMM-YYYY') AS MonYear,
It will separate the months and years as I want but If a visit crosses from one month into the next, all days from this visit will go in the starting month.

I the inline table below
Jan-2010 should = 3
Feb-2010 should = 3
Jan-2011 should = 3 ( it shows 4)
Feb-2011 should =2 (it shows1)

The fifth line in the table has a date range that crosses from Jan to Feb. The strange thing is I had this part working when I was using the month as the dimension.

Visits:
LOAD * INLINE [
VisitID, InpatientServiceID, AdmitDate, DischargeDate
2, MED, 01/01/2010, 01/03/2010
118, MED, 02/02/2010, 02/04/2010
1539, MED, 01/01/2011, 01/02/2011
1676, OOB, 02/01/2011, 02/01/2011
1671, MST, 01/31/2011, 02/01/2011
];

%%%% Question on Forum Procedure %%%%%%%%%%%%%%%
I asked this questions previously, received an answer and verified it. Since then I found an exception that I need to handle. In the future do I reopen the old thread somehow or start a new one?

[fosuzuki · Accepted solution]

Hi,

I think you should use this:

Date(makedate(year(AdmitDate + IterNo() - 1),month(AdmitDate + IterNo() - 1)),'MMM-YYYY') AS MonYear

Hope this helps you,

Regards,

* I'm not sure on how you shoud proceed, but If what you asked earlier was solved, and now you need another answer, i'd start another thread.

[Anonymous]

What if you try:

date(monthstart(AdmitDate), 'MMM-YYYY') AS MonthYear,

?

[Anonymous]

See Attachment.

Let me know if this works for you ok?

[#5]

Why not try:

MonthName(AdmitDate) ?

Regards

[dhborchardt]

Thanks.

Date(makedate(year(AdmitDate + IterNo() - 1),month(AdmitDate + IterNo() - 1)),'MMM-YYYY') AS xxMonYear,

This did the trick. If you look at the table (attached qvw) you can see that it properly handled the dates. I threw an additional visit in to test the change of year and it handled that event also.