Hi Ed,
Have you though of using the Previous() function?
It allows you to go back in time by creating, for instance a report showing
the current data for for your months side by side and do
the same for the PeriodSales 10, 12 or 13 months ago by doing
Previous(Previous(Previous…))… It becomes tricky as
you want to go far back in time but it does work.
Hope this helps.
Alain C. Bonnemaison.
Listserv Archives (BOB member since 2002-06-25)
In a message dated 99-02-19 11:45:35 EST, you write:
A second report will have only lists the current
fiscal period (Max Fiscal Month) and the next column needs to show the data
from the same period one year ago or 13 months ago.
Ed:
What you might want to do is see if you can create a “period offset” object in
your universe. You didn’t give any clues about your actual set up, so I’ll
make a few guesses.
Assume you have a fiscal calendar table in your database. You are likely to
have fiscal month, fiscal year, and so on as columns in this table. There is
often a way to determine the “current month” as well. If you have a period
number, and if you can identify your current month, you can use logic like
this:
Period Offset = Current Month Period Number - Period Number
If the date is in the current month, the offset is zero.
If the date is in the prior month, the offset is 1
If the date is two months ago, the offset is 2
If the date is one month in the future, the offset is -1
Two months in the future, -2
So, your query becomes…
and Period Offset between 0 and 23
And your logic in your report can be a filter on Period Offset of 0 and 13, or
whatever you desire. You can use the Sum() Where… functionality of BusObj
to do some neat things with this as well.
By creating a Period Offset object that is based on the fiscal calendar, when
a new month becomes “current” your report automatically updates. You do not
have to program specific dates or month names.
Hope this gives you some ideas…
Regards,
Dave Rathbun
Integra Solutions
www.islink.com
Listserv Archives (BOB member since 2002-06-25)