topic Re: Creating a prior Month in QlikView

Creating a prior Month

Tue, 22 Jan 2013 17:00:49 GMT

I'm trying to create a previous month in my script. I have a field that is "FiscalMthRelNum" that gives a number value (ex, 303 = Dec 2012). I'd like to create a PreviousFiscalMthRelNum that would return the value "302" with the record for Dec 2012. The "302" would indicate the previous month of Nov 2012.

Any ideas? Again, I'm trying to create this in the script, just not sure how.

Thank you for any help!

Re: Creating a prior Month

Gysbert_Wassenaar — Tue, 22 Jan 2013 18:51:13 GMT

Just subtracting 1 from FiscalMthRelNum should do the trick:

TableX:

load

FiscalMthRelNum,

FiscalMthRelNum - 1 as PreviousFiscalMthRelNum,

...

from ...etc;

Just make sure FiscalMthRelNum is a number. If necessary make it one first: num#(FiscalMthRelNum)

Re: Creating a prior Month

Anonymous — Tue, 22 Jan 2013 18:56:33 GMT

If you're using dates that were formatted by QlikView and each number represents 1 day rather than 1 month, I've found the AddMonths(Date,Offset) function helpful. So in your example, if you wanted previous month, you would put the date in the first part of the expression and the offset would be -1. But if you're generating you're own numbers for the months, then this may be overkill.