Solved: Populating 'MonthYear' from list of months and yea... - Qlik Community

Report Inappropriate Content · ‎2014-01-15

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

];

arbernardez · ‎2014-01-15

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

kiranmanoharrode · ‎2014-01-15

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;

Report Inappropriate Content · ‎2014-01-15

Hi Kiran,

That's throwing up this error:

Table not found

CalenderFinal:

Load Year,

Month,

MonthShort,

Month&'-'&Year as MonthYear

Resident Calender

arbernardez · ‎2014-01-15

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 · ‎2014-01-15

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

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

instead

Regards

Anonymous · ‎2014-01-15

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

Report Inappropriate Content · ‎2014-01-15

Perfect. Thank you so much.

Populating 'MonthYear' from list of months and years.