Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

What Dimension do I use?

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?

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

What Dimension do I use?

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.

View solution in original post

5 Replies
Highlighted
Partner
Partner

What Dimension do I use?

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.

View solution in original post

Highlighted
Partner
Partner

What Dimension do I use?

What if you try:

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

?

Highlighted
Partner
Partner

What Dimension do I use?

See Attachment.

Let me know if this works for you ok?

Highlighted
Not applicable

What Dimension do I use?

Why not try:

MonthName(AdmitDate) ?

Regards

Highlighted
Partner
Partner

What Dimension do I use?

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.