Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content
Announcements
WEBINAR April 23, 2025: Iceberg Ahead: The Future of Open Lakehouses - REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Populating 'MonthYear' from list of months and years.

Hi,

I'm having trouble finding the solution to this problem, and wondered if you could help!

I have created the start of a calendar, which displays a list of Years and their corresponding Months (and ShortMonths)

I want to be able to create a MonthYear field, which displays something like Jan-2005. I have read that this can be done by creating four flags for current month, previous month, current year and previous year, but I'm not exactly sure how to get it to work!

Below is my script so far.

Thanks.


Calendar:

LOAD * INLINE [

    Year

    2000

    2001

    2002

    2003

    2004

    2005

    2006

    2007

    2008

    2009

    2010

    2011

    2012

    2013

    2014

    2015

    2016

    2017

    2018

    2019

]
;

JOIN (Calendar) LOAD * INLINE [

    Month, MonthShort

    January,     Jan

    February,     Feb

    March,     Mar

    April,     Apr

    May,     May

    June,     Jun

    July,     Jul

    August,     Aug

    September,     Sept

    October,     Oct

    November,     Nov

    December,     Dec

]
;

JOIN (Calendar) LOAD * INLINE [

MonthYear, MonthShortYear

]
;

1 Solution

Accepted Solutions
arbernardez_old
Partner - Contributor III
Partner - Contributor III

Hello,

try this please, i think it will be useful for you:

YEARS:

LOAD * INLINE [

    YEAR

    2010

    2011

    2012

    2013

];

left join (YEARS)

MONTHS:

LOAD * INLINE [

    MONTH

    01

    02

    03

    04

    05

    06

    07

    08

    09

    10

    11

    12

];

YEARMONTHS:

load

YEAR,

MONTH,

date(makedate(YEAR,MONTH),'YYYYMM') as YEARMONTH,

text(date(makedate(YEAR,MONTH),'YYYY-MMM')) as YEARMONTHNAME

Resident YEARS;

drop table YEARS;

exit Script;

View solution in original post

6 Replies
kiranmanoharrode
Creator III
Creator III

Try Below script...................

it will solve your problem

Calender:

LOAD * INLINE [

    Year

    2000

    2001

    2002

    2003

    2004

    2005

    2006

    2007

    2008

    2009

    2010

    2011

    2012

    2013

    2014

    2015

    2016

    2017

    2018

    2019

]
;

JOIN (Calendar) LOAD * INLINE [

    Month, MonthShort

    January,     Jan

    February,     Feb

    March,     Mar

    April,     Apr

    May,     May

    June,     Jun

    July,     Jul

    August,     Aug

    September,     Sept

    October,     Oct

    November,     Nov

    December,     Dec

]
;

CalenderFinal:

Load Year,

Month,

MonthShort,

Month&'-'&Year as MonthYear

Resident Calender;

Drop Table Calender;


Not applicable
Author

Hi Kiran,

That's throwing up this error:

Table not found

CalenderFinal:

Load Year,

Month,

MonthShort,

Month&'-'&Year as MonthYear

Resident Calender

arbernardez_old
Partner - Contributor III
Partner - Contributor III

Hello,

try this please, i think it will be useful for you:

YEARS:

LOAD * INLINE [

    YEAR

    2010

    2011

    2012

    2013

];

left join (YEARS)

MONTHS:

LOAD * INLINE [

    MONTH

    01

    02

    03

    04

    05

    06

    07

    08

    09

    10

    11

    12

];

YEARMONTHS:

load

YEAR,

MONTH,

date(makedate(YEAR,MONTH),'YYYYMM') as YEARMONTH,

text(date(makedate(YEAR,MONTH),'YYYY-MMM')) as YEARMONTHNAME

Resident YEARS;

drop table YEARS;

exit Script;

arbernardez_old
Partner - Contributor III
Partner - Contributor III

if you prefer format for yearmonth as JAN - 2010 just use

text(date(makedate(YEAR,MONTH),'MMM-YYYY')) as YEARMONTHNAME

instead

Regards

Anonymous
Not applicable
Author

It's apparently a typo - "Calendar" in one place, "Calender" in another.  Make sure it is consistent.

Not applicable
Author

Perfect. Thank you so much.