Solved

Five weeks to date - starting 10 weeks ago

Posted on 2010-11-22
9
968 Views
Last Modified: 2016-02-15
Assuming I am part way through week n, I am trying to create a calculated measure that will return sales data from the following:
(week n-10) + (week n-9) + (week n-8) + (week n-7) + (part way through week n-6)

I think I need to separate this into two sections, (1) sum of weeks 10 to 7 and (2) getting the sales from week 6 up to the same point in thwe week as now.

I couldn't get a range statement to work so I have used a probably unnnecassarily verbose method to get the sum of weeks n-10 to n-7.
This is the code that failed:

SUM(
([R Year].[Time Reporting Week].CurrentMember.Lag(10):
[R Year].[Time Reporting Week].CurrentMember.Lag(7))
,[Measures].[GBP Total Gross])

Now I sum together together a series of parallel period functions:

SUM(
(ParallelPeriod([R Year].[Time Reporting Week], 9,[R Year].Currentmember))
,[Measures].[GBP Total Gross]      
) +             
SUM(
(ParallelPeriod([R Year].[Time Reporting Week], 8,[R Year].Currentmember))
,[Measures].[GBP Total Gross]      
) +      
etc.      

Now I am stuck with the last bit - finding sales in week n-6 up to the same point in the week that we are up to in week n. I think there's something wrong with the way that I am using the LAG method:

SUM(
PeriodsToDate(
[R Year].[Time Reporting Week],
[R Year].[Time Reporting Week].CurrentMember.Lag(5)                                          )
,[Measures].[GBP Total Gross]
)

The error message is
The CurrentMember function expects a hierarchy expression for the 0 arguement. A member expression was used.

This is the same error message I was geting with the failes weeks n-1- to n-7 calculation. I suspect it boils down to not knowing how to tell the LAG method that I want to go back a number of weeks rather than days, qurters or years.
0
Comment
Question by:LinksGu2
  • 4
  • 3
  • 2
9 Comments
 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 34189403
Let's break this down;

First you need the start point for your date range.

SELECT DATEADD(wk, -10, GETDATE())

That'll take 10 weeks off our current date. Trouble is, that'll be an exact date, which I'm assuming we don't need. So let's flatten the time off the date;

SELECT CAST(FLOOR(CAST(DATEADD(wk, -10, GETDATE()) AS FLOAT)) AS DATETIME)

That's exactly 10 weeks ago. We now need the start of that week (I'm going to assume Monday but play with the subtraction as you need to;)

SELECT CAST(FLOOR(CAST(DATEADD(wk, -10, GETDATE() + 1) AS FLOAT)) AS DATETIME) - (DATEPART(dw, GETDATE()) - 1)

We then copy this formula into the GROUP BY clause, but edit it for 5 weeks;

SELECT COUNT(*)
FROM MYTABLE
WHERE MYTABLE.Date >= CAST(FLOOR(CAST(DATEADD(wk, -10, GETDATE() + 1) AS FLOAT)) AS DATETIME) - (DATEPART(dw, GETDATE()) - 1)
GROUP BY CASE WHEN MYTABLE.Date >= CAST(FLOOR(CAST(DATEADD(wk, -5, GETDATE() + 1) AS FLOAT)) AS DATETIME) - (DATEPART(dw, GETDATE()) - 1) THEN 1 ELSE 0 END

This query will show you a count for your first 5 weeks, and the subsequent 5 weeks. It's up to you now to do your own aggregation for your sums etc but this should do what you need with regards to grouping. (If I understood your question correctly)
0
 

Author Comment

by:LinksGu2
ID: 34189608
Thanks for your help but I think you have offered a T-SQL solution. This is an MDX Analysis Services problem. I put MDX in the tags section but couldn't find anything relevent in the Zone section. Aplologies for the confusion.
0
 
LVL 10

Expert Comment

by:thefritterfatboy
ID: 34189627
No probs - afraid MDX is something I know absolutely nothing about so can't help you with that.
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34203345
Hi, you say you couldn't get a range to work... did you try:

...CurrentMember.Parent.Lag(10).FirstChild: ..CurrentMember.Lag(35)

I'm assuming that the current member is a date (otherwise you can't position youself in the current week). Key to the above is getting the start date - this gets the parent week of the current date, goes back 10 weeks and gets the first date within the week (i.e. start of 10 weeks ago).

Tim
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:LinksGu2
ID: 34203871
That looks promising and I will give it a go.
The report that I am creating has calculated meassures that refer to both weeks and months. Is there a way of directly referring to the level of the hierarchy that I want the Lag to work on, rather than knowing that I have to climb up one level to get to weeks and two levels to get to months?

0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34203990
Yes, check out the Ancestor function - let's you specify the level of 'Parent' that you want to reference.

Tim
0
 

Author Comment

by:LinksGu2
ID: 34204105
I wanted to start testing the Parent and Ancestor suggestions but I'm afraid I've fallen at the firsdt fence. I wanted to check that I could get a basic range to work before I put in the Ancestor and Parent constructs but I couldn't. the code below, which I'm running in Management Studio, gives the following error:

The function expects a string or numeric expression for the arguemebnt. A tuple set expression was used.

This is the code that produced the error:
MEMBER [Measures].[Three Month Total] AS
((
[Date].[Calendar].CurrentMember.Lag(4):[Date].[Calendar].CurrentMember.Lag(1)
),
[Measures].[Reseller Sales Amount])


However, this worked:
MEMBER [Measures].[2 Months Ago] AS
      ([Date].[Calendar].CURRENTMEMBER.LAG(2), [Measures].[Reseller Sales Amount])
0
 
LVL 15

Accepted Solution

by:
Tim Humphries earned 250 total points
ID: 34205394
I think you would need to use the following structure:

MEMBER [Measures].[Three Month Total] AS
SUM(
       {[Date].[Calendar].CurrentMember.Lag(4):[Date].[Calendar].CurrentMember.Lag(1)},[Measures].[Resller Sales Amount]
);

0
 

Author Closing Comment

by:LinksGu2
ID: 34237573
x
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now