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

[#1]

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

[swuehl]

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)

[#3]

I tried that and got a null answer.

[swuehl]

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

[#5]

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

Thanks!

[swuehl]

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

[#7]

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?

[swuehl]

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

[jduenyas]

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

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

[#10]

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