Solved

Five weeks to date - starting 10 weeks ago

Posted on 2010-11-22
9
971 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 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