Grouping by a billing month 1

[paulosinuk]

Hi,

I have a report that is grouping by month on a date/time field. The problem is that the company want to be able to report by 'billing month' rather than 'calender month'.

A billing month runs the period of 24th of previous month to 25th of current month. So for example February would be 24th January to 25th February.

The report contains 18 months of calender history which needs to be converted. Furthermore, this is going to be used on a rolling basis so I can use the typical approach of:

if date >= x and date <= y then Z

because I would have to code well into the future in order to future proof.

Is there any way to create a group based upon this type of range?

Thanks

Paulos

 

[Reebo99]

the logic doesn't seem to fit. If you go from the 24th to the 25th each month then :

Feb = 24th Jan to 25th Feb
Mar = 24th Feb to 25th Mar

are you counting the 24th twice? Reebo
Scotland (Going mad in the mist!)

 

[paulosinuk]

you're right should have been

if date >=25th and date <=24th then Z

but ultimately I don't want to write code along these lines because I need something that is adaptive to a 18 month rolling history.

 

[Reebo99]

OK,

You can bring each record down to a common date then group on this, e.g. :

if day(datetimefield) <= 24 then date(year(datetimefield),month(datetimefield),01) else (year(datetimefield),month(datetimefield)+1,01)

You have to allow for months going over the end of the year, but this will give you a start.
Reebo
Scotland (Going mad in the mist!)

 

[synapsevampire]

Try:

if day(datetimefield) <= 24 then
date(year(datetimefield),month(datetimefield),01)
else
datediff(&quot;mm&quot;,1,date(year(datetimefield),month(datetimefield),01))

Should do it.

-k