Five weeks to date - starting 10 weeks ago

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.
LinksGu2Asked:
Who is Participating?
 
Tim HumphriesConnect With a Mentor DirectorCommented:
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
 
thefritterfatboyCommented:
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
 
LinksGu2Author Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
thefritterfatboyCommented:
No probs - afraid MDX is something I know absolutely nothing about so can't help you with that.
0
 
Tim HumphriesDirectorCommented:
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
 
LinksGu2Author Commented:
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
 
Tim HumphriesDirectorCommented:
Yes, check out the Ancestor function - let's you specify the level of 'Parent' that you want to reference.

Tim
0
 
LinksGu2Author Commented:
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
 
LinksGu2Author Commented:
x
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.