Date variable does not work with January

[Anonymous]

I have been using date variable within my report to show record counts between this month and last month. In my report I am always showing last months data against the previouse month. I am using (Today) as a starting point for my variables.

So for example in the month of December I was displaying November and October and comparing values. Using Todays date -1 to equal vLastMonth and Todays -2 to equal vLastmonth2. Because January is month 1 my last month variable is 0 and vLastMonth2 is negative 1. So my charts are not working today.

Below are my variables.

LET

LET vLastMonth=num($(vThisMonth-1);

LET

LET

LET

vLastYear=num($(vThisYear)-1); vThisYear=num(year(today())); vLastMonth2=num($(vThisMonth)-2); vThisMonth=num(Month(Today()));

Can anyone tell me how to set my variable up in a better way that will allow me to get the sum of records for December.

Thank you.

[#2]

For this sort of thing I tend to use MONTHNAME().

It returns a date time stamp that corresponds to the first of the month for the date contained in the parens, and has an optional parameter referred to as [shift] that indicates the number of months to shift in either direction.

So, you could use MONTHNAME(TODAY(0), -1) for 'last month' for example, and re-write your sets to look from the start of last month to the start of 'this month' (determined the same way)

You may need a little script and object tweaking, but you'll be able to handle year roll overs gracefully.

-R

[#3]

Oh, I should mention, in case it's not obvious, because it's a datetime, it includes the year by default. so, MONTHNAME(01/01/2011, -1) would equal Dec 1 2010.

You'll need to include a DATETIME(<field>) in your load script to compare against on your fact/dimension table.

Your sets get a bit trickier, too... but there is lots of forum help for date sets. (Or re-post, or msg me if you have issues)

[johnw]

As you can probably see, the format you pasted in makes it very difficult to see what you're currently doing. Either cust and paste to notepad before cutting and pasting again to the forum, or go into settings, user preferences, editor, and remove the checkmark next to "Copy as RTF".

On to your real question, I likely wouldn't be using variables in the first place, but instead making the connections between months with data, or just putting the logic in a set analysis expression.

But you appear to have an emergency fix required, so let me take a stab at it with variables so that hopefully they are the ONLY thing you need to change. If I've understood what you want your variables to be, perhaps this?

LET vThisYear = year(today());
LET vThisMonth = num(month(today()));
LET vLastYear = vThisYear-1;
LET vLastMonth = if(vThisMonth=1,12,vThisMonth-1);
LET vLastMonth2 = if(vLastMonth=1,12,vLastMonth-1);

[Anonymous]

Thank you so much for you help. It worked perfectly. I was in a time pinch but I would like to explore why you said I shouldnt be using variables.

I apologize for my horrible pasting. This was my first post.

Thank you again, Cassadi

[johnw]

cassadi44 wrote:I would like to explore why you said I shouldnt be using variables.

Well, it sounds like you're using the variables to compare the last three months of data. There are typically better ways to handle that sort of requirement. I'm unclear how you're using the variables, so it's hard for me to be specific. Still, just as an example of a data solution, you might do something like the below (untested):

[Last Three Months]:
LOAD
pick(recno(),'This Month','Last Month','Two Months Back') as Period
,date(monthstart(today(),1-recno()),'MMM YYYY') as YearMonth
AUTOGENERATE 3
;

Chart Dimension = Period // Sort in original load order. And in a pivot table, you might want to move this to the top of the chart.
Chart Expression = count(distinct ID)