I have a report that looks at 12 months worth of sales figures but is offset by 2 months so, for example, if I run the report today (Feb 2012) it returns data from Jan 2011 to Dec 2011 which is exactly how I want it.
I have a MAT and a YTD and these calculations have been working very well previously until I got to 2012! Some of my calculations use ‘current year’ to return the YTD data. Obviously, now we are in 2012 it’s failing to return a value.
I have tried various ways to get the year from the date of 2 months ago but I can’t work out how to do it.
Does anyone have a possible solution that could help me?
I am using an SQL database and my universe pulls data from a very basic setup. I have a master table with sales lines in it (flat) and some extra tables for geographics, sales targets and product groups.
Over the weekend, I had an idea which seems to work but I dont know if it was the right solution or if it’s going to fall over in the future.
I have created an object in my universe and in its SELECT section, I have added the following to return the year from 2 months ago:
YEAR(getdate()-63)
I guessed that 63 days ago will always be 2 months (might there be some issues with Febuary?) I then use this object in my YTD target query.
So - I have gone from this:
=[Percentage_Of_Plan] Where ([Year]=Year(CurrentDate()))
to this:
=[Percentage_Of_Plan] Where ([Year]=[YearFix]))
Was this the right approach? What are your thoughts?
year(dateadd(m,-2,cast(convert(char(7),getdate(),23)+'-01' as datetime)))
Which is obviously bullet proof! I assume that where I am utilising it in the universe is correct?
Sorry to post a dim question, I’m not a full time SQL or BI developer but I was landed with a half finished BO solution 6 months ago having never used BO before and only being a ‘now and again’ SQL user. The last 6 months have been steep.
That solution is so obvious now I can see it - d’oh!
(BOB member since 2012-02-17)