dbTalk Databases Forums  

How to use filter with Lag ?

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap


Discuss How to use filter with Lag ? in the microsoft.public.sqlserver.olap forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Kaisa M. Lindahl
 
Posts: n/a

Default 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



Reply With Quote
Reply




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.