topic Re: Dynamic month naming in QlikView

Dynamic month naming

Fri, 29 Jul 2011 09:47:29 GMT

Hi All,

I’ve got a sales data with headers (expressions);

M0 – M1 – M2 – M3 – M4 – M6 – M7 …

M0 is current month, M1 is last month, M2 is the month before, etc..

How can I turn these into month names? Basically I’m askingfor dynamic month naming.

I've tried M0 = month(today()) which works then tried M1 = month(today()-1 which didn't work.. any idea??

Thanks,

Mem

Dynamic month naming

Anonymous — Fri, 29 Jul 2011 10:58:32 GMT

You could try something like this in your script:

(Month(Today())- (right(Monthfield,1))) as Month,

This will return number of the right month.

But you have to modify it to your own script, but maybe you can start from here.

And keep in mind that you could better solve it on a MonthYear field in stead a MonthField.

Dynamic month naming

Fri, 29 Jul 2011 11:53:43 GMT

Hi Dennis,

Unfortunately that didn't work. But I've managed to get a number output by writing;

=Month(today())-Month(30) This gives a result of 6 (Jun)

=Month(today())-Month(60) This gives a result of 5 (May)

=Month(today())-Month(90) This gives a result of 4 (Apr)

and so on ...

If I could turn these numbers into month names that would solve my problem I guess..

Cheers,

Mem

Re: Dynamic month naming

swuehl — Fri, 29 Jul 2011 12:21:53 GMT

Hi,

try

=month(makedate(2011,month(today())-1,1))

for M1

=month(makedate(2011,month(today())-2,1))

etc.

P.S:

I would not use "month(today()) - month(30)" to get the previous month, but month(today())-1

Both result in 6 (as of today), buth month(30) means month('1900-01-29') which is Jan (or 1), but I think this is misleading and depending on start of internal calendar.

In above expression, I make a date (because month() expects a date, using calculated month number and arbitrary year and day number (i.e. 2011, 1)

Dynamic month naming

SunilChauhan — Fri, 29 Jul 2011 12:39:38 GMT

month(addmonths(today(),-1))

for Month in text like jan feb

num(month(addmonths(today(),-1))) for 1,2,3 ,4

thanks

Sunil Chauhan

Re: Dynamic month naming

Fri, 29 Jul 2011 12:50:27 GMT

Thanks both,

They both do the job except Swuehl's method stops working for "-7" (Dec-2010) since the data is for 18 months.

Sunil's works perfectly.

Cheers,

Mem