How to use filter with Lag ? -
12-15-2005
, 10:17 AM
How can I use filter in combination with lag?
I have a time based dimension, consisting of Year and Week. My users wants
to choose a week in one year, and see the last 4 weeks for that week and
that year AND the same for the previous year. They pick the week based on
existing weeks for the given year, so the first year and week combination
will always work. But if the chosen week does not exist in the previous
year, the whole query fails. I want to make sure that I at least get the
numbers for this year, and that the query doesn't fail.
The following code is similar, using Foodmart 2000, and shows Quarters.
with
set [dates] as 'Generate({[Time].[1997].[Q4]:[Time].[1997].[Q4].lag(3),
[Time].[1998].[Q4]:[Time].[1998].[Q4].lag(3)}, {Time.CurrentMember})'
set [rows] as 'Descendants([Product].[All Products].[Drink].[Beverages],1)'
select [dates] on columns,
non empty { [rows] } on rows
From [Sales]
where ([Measures].[Unit Sales])
It works for Time.1997 and Time.1998, for Q1 to Q4. But if I try to cange Q4
to Q5, it fails. I need to make sure that the query doesn't fail, even if I
try to use a Quarter that doesn't exist. I did make a query using Filter:
with
set [dates] as 'Generate(
{filter([Time].[1997].children,
[Time].Currentmember.properties("Name")="Q5"),filter([Time].[1997].children,
[Time].Currentmember.properties("Name")="Q2"),
[Time].[1998].[Q4]:[Time].[1998].[Q4].lag(3)}, {Time.CurrentMember})'
set [rows] as 'Descendants([Product].[All Products].[Drink].[Beverages],1)'
select [dates] on columns,
non empty { [rows] } on rows
From [Sales]
But when adding the filter clause, I couldn't figure out where to put the
lag(), and then I just added a new filter statement for each week, like Week
10, Week 9 etc. The problem with this is that even though it works, it
basically won't work the first 3 weeks of a year...
Any tips on how to make sure I get a result, even if the week I picked does
not exists in the previous year?
Kaisa M. Lindahl |