YTD offset by 2 months

[system]

Hi,

This is my first post in the BOBs so here goes…

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?

Thanks in advance,
James

---

James P (BOB member since 2012-02-17)

[system]

Welcome to B:bob:B!

So are you after a universe solution or a report solution?

---

Nick Daniels (BOB member since 2002-08-15)

[system]

Hi Nick,

I don’t mind using either. I suppose it comes down to which ever is easiest / more flexible.

---

James P (BOB member since 2012-02-17)

[system]

The universe is the way to go - what database are you using and what have you got at the moment in thw way of sql.

---

Nick Daniels (BOB member since 2002-08-15)

[system]

I thought as much

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?

---

James P (BOB member since 2012-02-17)

[system]

Try this:

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server. Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8. Yesterday cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) Today cast(convert(char(10),getdate(),23) as datetime) The following also works for today: dateadd(dd, datediff(dd,0,getdate()), 0) Start of Current Month cast(convert(char(7),getdate(),23)+'-01' as datetime) End of Current Month dateadd(d,-1…

---

Nick Daniels (BOB member since 2002-08-15)

[system]

Eureka! Thanks for the pointer Nick.

I am now using:

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!

Thanks again - much appreciated,

James

---

James P (BOB member since 2012-02-17)

[system]

Yep - in the universe using a pre-defined condition - good luck 8)

---

Nick Daniels (BOB member since 2002-08-15)