I have a request to create automated reports where the start date is July 1, 2010. (Financial year July to June). The query hard codes that date so the reports can run automatically each month and then be sent by email to various inboxes. The user would like the minimum number of separate reports so I am trying to group like dimensions into a series of reports that run off one query. At the moment I have 6 different queries covering 2 dozen reports.
I’ve created a variable Max Date =Max([Presentation Date]).
In some of the reports I’ve been asked to create, the user would like to compare YTD activity from the current year and the previous year.
I’m having difficulty trying to create variables that will identify the months/years that make up the current year and the previous year. Presumably the query will keep running the same 4 years from now with the start date of July 1, 2010 and I would like to only show in some reports the current and previous years data. Other reports show each consecutive month’s data.
I need to create a report that can show July to Dec 2010/11 as compared to July to Dec 2011/12. I had originally set up reports with prompts for start and end dates, but my manager would like me to fully automate the reports so that no dates need to be selected.
I’m having problems figuring out how to extract information from the max date so that I can group it into financial years. I would also like to extract the month from the max date so that I can set a filter for the maximun month of the current financial year. At the moment I am getting a max month of 12 (June) rather than 6 (December). I understand that is happening as the current query has 18 months worth of data. I’ve had a look at some date functions but can’t seem to find the right method to get the information I need.
Has anyone done something similar with dates? If so how did you handle extracting the required information from the MaxDate?
I have had a similar requirement and created a table with the dates and fiscal year and other relevant info.
That way I could reference each date and its corresponding financial year, quarter, month, week, etc.
It also made a comparison of YTD values of different years easier.
Only problem was to create the table once for the entire reporting period.
Also keep in mind to update the table whenever an new/larger/later reporting period is required.
Hope that helps, although it is not a solution with a variable…
I don’t think that solution will work for me. I don’t have access to the universe.
I had a suggestion to create a second query with just the year and month and to untick “retrieve duplicate rows”. I did this then tried to create a variable that would determine the latest month in the latest financial year.
I tried MaxFinYr=Max([Query 2].[Presentation Financial Year]) which returns 2012. I then tried to obatin the Maximum Month in the Presentation Financial Year by using the variable =Max([Query 2].[Presentation Financial Period]) Where ([Query 2].[Presentation Financial Year]=Max(([Query 2].[Presentation Financial Year]))). I expected this to return a value of 6, however it returned 18 (which is 12 for 2011 and 6 for 2012).
I also tried =Max([Query 2].[Presentation Financial Period]) Where ([Query 2].[Presentation Financial Year]=[MaxFinYr]) which also returned 18 instead of 6.
Can anyone suggest where my logic is wrong? I can’t just subtract 12 months from the result as the eventual report will run over the next couple of years and the 12 will become 24 then 36.
There are lots of posts on BOB about previous year vs. current year. (See partial list below.) You could also try context operators, like “In [Year]” e.g., Max([Date]) In [Year].
Here are a couple of posts about current year vs. previous year:
Many of these posts suggest separate data providers.
(BOB member since 2003-06-20)
(BOB member since 2003-06-20)
(BOB member since 2011-05-16)
(BOB member since 2002-06-20)