Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

lag function calculation Expand / Collapse
Author
Message
Posted Thursday, September 11, 2008 8:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 28, 2011 12:43 PM
Points: 6, Visits: 71
Hi I have situation



I am trying to calculate lag period from the present quarter to last quarter



time hierarchy has

year(level1)

quarter(level2)



i wanted to see percent rejected measure in such a way that



2007Quarter4 -''percent rejected" shows 2007Quarter3 value

and

2007Quarter3 -''percent rejected" shows 2007Quarter2 value

2007Quarter4 -''percent rejected" shows 2007Quarter3 value

and

2007Quarter1 should show -''percent rejected" shows 2006Quarter4 value( I am not able to see these values

)

when I gave the above code:
( [Time].[TimeHierarchy].CurrentMember.Lag(1), [Measures].[percent rejected] )



this is fine with year level ,but at quarter level...for example 2008 Q1 is not picking up the value of 2007 Q4
it shows null values

when I try this above code

iif( [Time].[TimeHierarchy].CurrentMember.Level.Ordinal = 2, (([Time].[TimeHierarchy].CurrentMember.Lag(1)),[Measures].[percent rejected] ),
(([Time].
[TimeHierarchy].CurrentMember.Lag(1)), [Measures].[percent rejected] ))

still the same thing happens in the result


Post #567813
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse