Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
I tried that and got a null answer.
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
I think that fixed the previous mtd, but I am still out for the current mtd.
Thanks!
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
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?
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
Try this in a text object to test the function: =monthstrat(today())
It should return, of course, 1/1/12
I didn’t even think to do that. Why in the world would it be 12/1/11?!