Getting This week and last week data

Question

• Hello,

I have booking fact table and I want to see how many booking the employees booked this week compare to last week. I tried many examples I found in the forum but not getting the right results.

I tried:

```WITH
SET LastWeek AS ClosingPeriod([Book Date].[Week Number Of Year].[Week Number Of Year], [Book Date].[Week Number Of Year].currentmember)
SET Last13Weeks AS {LastWeek.Item(0).Lag(2):LastWeek.Item(0)}
MEMBER MEASURES.IIFDEMO AS IIF([Measures].[No Pax]>0, [Measures].[No Pax], "0")
SELECT Last13Weeks*MEASURES.IIFDEMO ON 0,
NON EMPTY ORDER(FILTER ( [Pax Report].[Booked By].[Booked By] , MEASURES.IIFDEMO <> "0") , MEASURES.IIFDEMO,DESC) ON 1
FROM [Cube]
WHERE {[Pax Report].[Res Status].&[A]}
```

But then I get the last weeks from the list: 8 and 9 (because it is acting as string so my weeks go 1,10,11,12,2...8,9)

Thanks for the help

Tuesday, July 23, 2013 7:48 PM

All replies

• Hi

To my understanding of your question (sorry, bit not clear to me)

1. You need to get the last week member and previous last 3 weeks information

try this,

```WITH
MEMBER LastWeek AS
TAIL( nonempty( [Book Date].[Week Number Of Year].[Week Number Of Year].Members ,[Measures].[No Pax] )).ITEM(0)

SELECT { [Measures].[No Pax] } ON 0,

NON EMPTY {
ORDER ( {
[Pax Report].[Booked By].[Booked By].Members *
{   LastWeek
: LastWeek.lag(2)
}
}
, [Measures].[No Pax]
, DESC
)

}     ON 1
FROM [Cube]
WHERE {[Pax Report].[Res Status].&[A]}```

Prav

Wednesday, July 24, 2013 6:43 AM
• seems like a sorting issue with your attributes to me

can't you convert your relational column to int and sort the final Attribute "by Key"?

- www.pmOne.com -

Wednesday, July 24, 2013 7:08 AM
• Hi

To my understanding of your question (sorry, bit not clear to me)

1. You need to get the last week member and previous last 3 weeks information

try this,

```WITH
MEMBER LastWeek AS
TAIL( nonempty( [Book Date].[Week Number Of Year].[Week Number Of Year].Members ,[Measures].[No Pax] )).ITEM(0)

SELECT { [Measures].[No Pax] } ON 0,

NON EMPTY {
ORDER ( {
[Pax Report].[Booked By].[Booked By].Members *
{   LastWeek
: LastWeek.lag(2)
}
}
, [Measures].[No Pax]
, DESC
)

}     ON 1
FROM [Cube]
WHERE {[Pax Report].[Res Status].&[A]}```

Prav

I'm trying to get something ever easier. Compare this week to last week number of booking by sales ref.

When I run your query I get error:

Executing the query ...
A calculated member cannot be used as an operand of the Range operator ( : ).
Execution complete

Wednesday, July 24, 2013 12:39 PM
• seems like a sorting issue with your attributes to me

can't you convert your relational column to int and sort the final Attribute "by Key"?

- www.pmOne.com -

Hi Gerhard,

I'm not sure why SSAS treats the week's column as string.

In the DB the [WeekNumberOfYear] column is tinyint.

Any ideas why?

Thanks

Wednesday, July 24, 2013 12:44 PM
• there is a property of the Attribute called "OrderBy"

it should be "Key"
if it is "Name" it may happen it is sorted in alphanumeric order

- www.pmOne.com -

Wednesday, July 24, 2013 1:29 PM
• there is a property of the Attribute called "OrderBy"

it should be "Key"
if it is "Name" it may happen it is sorted in alphanumeric order

- www.pmOne.com -

Thank you, it doesn't sort by the correct values.

But still gives me the end of the year.

How do I get this week and last week.

When I do:

```WITH
SET LastWeek AS ClosingPeriod([Book Date].[Week Number Of Year].[Week Number Of Year], [Book Date].[Week Number Of Year].currentmember)
SET Last13Weeks AS {LastWeek.Item(0).Lag(4):LastWeek.Item(0)}```

I get the last four weeks of the year (weeks 50-53), not this week and previous three.

Also, I'm getting a sum of booking for these weeks for all years (so instead of summing this year values, I get from 2008- today for specific week)

Wednesday, July 24, 2013 2:29 PM
• well, according to your Attributename [Week Number Of Year] i would assume that this is Independent of years

this means [Week Number Of Year].&[1] would be an Aggregation over all 1st Weeks of all years

couldn't you simply add the year into the WHERE-clause of your query??

- www.pmOne.com -

Wednesday, July 24, 2013 3:26 PM
• Yes of course I can. But how do I add:

`Where [Book Date].[Calendar Year] = This Year`

To my where clause? I'm not sure how to make it dynamic.

However, it's still not giving me the right result set. Today is week 30 of the year. The result should be weeks 27-30 not 50-53.

Also, what will happen if we run this query on the first week of the year? it will not be able to compare to last week since last week is a different year.

Thanks again

Wednesday, July 24, 2013 3:34 PM