Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 978
  • Last Modified:

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.
0
LinksGu2
Asked:
LinksGu2
  • 4
  • 3
  • 2
1 Solution
 
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
 
thefritterfatboyCommented:
No probs - afraid MDX is something I know absolutely nothing about so can't help you with that.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Tim HumphriesCommented:
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 HumphriesCommented:
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
 
Tim HumphriesCommented:
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
 
LinksGu2Author Commented:
x
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now