
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Last Full Month
Hi,
I am trying to run a report for last full month regardless of the date
For example if I run the report now I would like to retrieve May's data. It also needs to be dynamic.
Any help would be much appreciated
- « Previous Replies
- Next Replies »
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this:
sum({<[Bill Date]={"<$(=MonthStart(today())) >=$(=MonthStart(addmonths(today(),-1))) "}, Month= >} [Bill Value] )

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Dave,
Can you post a copy of your report as it exists now. Usually you would use AddMonths function to achieve this but if I can see what you're trying to accomplish, I might be able to help more.
AddMonths(Max(YourDate),-1)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
if you Need to run your Report with last months data you have two Options depending on your datamodel
1) you have field which contains full date Format (day, month, year)
then you Need to run your Report with
(yourdatefield >= monthstart(addmonths(today(),-1)) and (yourdatefield <= monthend(addmonths(today,-1))
2) if you have a month field and year field
(youryearfield = year(addmonths(today(),-1)) and (youmonthfield = month(addmonths(today(),-1))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Dave,
Make a variable with this code:
If(YourDate = MonthEnd(YourDate),YourMonth,Month(Addmonths(YourDate),-1))
With this code you will test if current month is complete, if it is true you will get current month, else, you will get last complete month.
Select your report with this var.
Regards!!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you might need a 'floor' in this to be sure no time element causing issues...
if(YourDate=Floor(MonthEnd(YourDate)), Floor(MonthEnd(YourDate)), Floor(MonthEnd(AddMonths(YourDate,-1))))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
If the field is date format, and not is TimeStamp format, you don't need to use Floor function.
Regards.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
this errors after the and ay ideas?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
can you post sample?


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Dave,
Try this expression,
sum({<Date={">=$(vMonthStart)<=$(vMonthEnd)"},Month=,Quarter=,FullYear=>} Amount)
Here
vMonthStart=Date(MonthStart(Max(@_Date)),'DD/MM/YYYY')
vMonthEnd=Date(Max(@_Date),'DD/MM/YYYY')
Susovan

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sum({<[Bill Date] {">= monthstart(addmonths(today(),-1)) and [Bill Date] <= monthend(addmonths(today(),-1))"} > [Bill Value])
I am trying to sum bill value by bill date

- « Previous Replies
- Next Replies »