Solved

Five weeks to date - starting 10 weeks ago

Posted on 2010-11-22
9
973 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Grid querry results 41 86
Whats wrong in this query - Select * from tableA,tableA 11 58
Need to find substring in SQL 5 52
Can I skip a node in XML? 9 46
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

752 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