Joined: 05 Feb 2010
Online Status: Offline
Posts: 7
Topic: Compare separate period and year to currentdate Posted: 26 Jul 2010 at 9:46am
Hello,
I am designing a report to show sales by customer by period rotating based on what is the current month. The data I am using separates out Fiscal Period and Fiscal Year into separate fields. It would look a little like this:
Customer Name
July June May April etc..... extending back for a year
next month it would look like
August July June May etc..... extending back for a year
The problem I am having is getting the formulas to recognize when it is no longer the current fiscal year based on the date. Here is the formula I have so far:
Current month:
if (tonumber ({AR_CustomerSalesHistory.FiscalPeriod})) = Month(dateadd('m',0,CurrentDate)) then {AR_CustomerSalesHistory.DollarsSold}
I subtract from the dateadd formula to go back in time by each month but have a problem when I need it to discern between the current fiscal year and previous fiscal years.
Is there a way to combine the fiscal period and fiscal year using a formula, then equate that to the current date? How will this work when I get back to December and it should pull data from the previous fiscal year?
If anyone has any suggestions on how this could work that would be great!
Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Posted: 26 Jul 2010 at 9:57am
My solution to something similar was to create 12 separate SQL Select queries using a parameter for the period and wrapping the queries in a select case statement with the case being the parameter for the period. I also had some logic to make sure the accounting period was reporting the correct month and year.
The client I did this for had a fiscal year that ended January 31. They also wanted a current year to previous year comparison. A fair amount of code but it works.
Joined: 05 Feb 2010
Online Status: Offline
Posts: 7
Posted: 27 Jul 2010 at 7:36am
Could you give me an example of what your query looked like? I haven't used the SQL Select before, have used more formulas but can probably get this if I have a place to start.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
