Solved

SQL 2008 SSAS MDX formula

Posted on 2014-12-16
4
187 Views
Last Modified: 2016-02-18
What's the purpose of 'ANCESTOR' and .lag(1) used in the following mdx formula ? I have attached our current data dimension for reference, Tks


([Measures].[BaseAmount],ANCESTOR( [DIM DATE].[Year Group].CurrentMember, [DIM DATE].[Year Group].[Year] ).lag(1))
DateDimension.png
0
Comment
Question by:AXISHK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40502167
It would appear that it is giving you the BaseAmount for the whole year before where you currently are:

Where are you currently in the Year Group hierarchy? Wherever it is, go back up to the Year. (So, if you were in 14 Feb 2014, go to the year 2014). (That's the ANCESTOR.)

Then go back a year (so, go to the year 2013). (That's the LAG)

Is that reflective in what you see as the result?
0
 

Author Comment

by:AXISHK
ID: 40502384
attached the data dimension for your reference. Tks
DateDimension.png
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40502386
Yes, I've seen that. My question was:

Is that reflective in what you see as the result?
0
 

Author Closing Comment

by:AXISHK
ID: 40503941
Correct, tks
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question