13 Replies Latest reply: Jan 4, 2012 6:32 PM by Kristy McGee

# Set Analysis monthstart(today()) does not work in January

I am currently using these formulas to pull the current month and previous mtd.  For January I am getting some crazy numbers.

Current Mtd:

sum({<Tran Date]={">=\$(=monthstart(today()))"}>}Fees)

Previous month to date:

sum({<Tran Date]={">=\$(=monthstart(max(trandate),-1) & '<=' & Addmonths(max(trandate),-1))"}>}Fees)

If someone could help me adjust these formulas to pull correctly, I would greatly appreciate assistance.

Thanks,

Kristy

• ###### Set Analysis monthstart(today()) does not work in January

First, try without the & '<=' &, with an additional \$ sign expansion and some brackets fixed:

sum({<Tran Date]={">=\$(=monthstart(max(trandate),-1))<=\$(=Addmonths(max(trandate),-1))"}>} Fees)

• ###### Re: Set Analysis monthstart(today()) does not work in January

I tried that and got a null answer.

• ###### Set Analysis monthstart(today()) does not work in January

Ok, I noticed that there might be an opening [ missing on [Tran Date] ?

Then, is this the same field you are calculating the max on? Then you need also max([Tran Date]).

Finally, you might need to check the Format of the comparison between field [Tran Date] and your set element list you get back from your search expression. Date format must match!

Or use numerical representation instead:

=sum({< [Tran Date] = {">=\$(=num(monthstart(max([Tran Date]),-1)))<=\$(=num(Addmonths(max([Tran Date]),-1)))"} >} Fees)

Hope this helps,

Stefan

• ###### Re: Set Analysis monthstart(today()) does not work in January

I think that fixed the previous mtd, but I am still out for the current mtd.

Thanks!

• ###### Set Analysis monthstart(today()) does not work in January

Also probably some format issues with comparing your [Tran Date] field and your search expression result? Try

sum( {<Tran Date]={">=\$(=num(monthstart(today())))"}>} Fees)

Have a nice evening,

Stefan

• ###### Re: Set Analysis monthstart(today()) does not work in January

This is giving me a 0 result.  If I use my existing formula, I get a very high result.  (250k instead of 50k)  Could this be because I have 2 January’s listed, 2011 and 2012?

• ###### Set Analysis monthstart(today()) does not work in January

It should work quite nicely with multiple years.

But again, I missed the opening square bracket:

sum( {< [Tran Date]={">=\$(=num(monthstart(today())))"}>} Fees)

[Tran Date] field is of recognized date type, with a numerical representation?

Hm, maybe the num() is not a good idea here, try

sum( {< [Tran Date]={">=\$(=Date(monthstart(today())))"}>} Fees)

your standard date format should match the [Tran Date] format.

Hope this helps,

Stefan

• ###### Set Analysis monthstart(today()) does not work in January

Try this in a text object to test the function:  =monthstrat(today())

It should return, of course, 1/1/12

• ###### Re: Set Analysis monthstart(today()) does not work in January

I didn’t even think to do that.  Why in the world would it be 12/1/11?!

• ###### Set Analysis monthstart(today()) does not work in January

Check your systems calendar/date.

Your are still living in last year. :-)

• ###### Set Analysis monthstart(today()) does not work in January

Why in the world would it be 12/1/11?!

Another possibility:

today() is equivalent to today(2), which means: Date when the document was opened

I assumed that you have opened the document after Jan 1st, but maybe you have still kept it open since last December?

• ###### Set Analysis monthstart(today()) does not work in January

Brilliant!

Now I understand why I do not get good data when I keep my application open over night or over a weekend.

Thanks a lot.

• ###### Re: Set Analysis monthstart(today()) does not work in January

HAHA!  That is why it says today=12/15.  It stays open on the server.

Thank you so much,

Kristy