Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL 2008 SSAS MDX formula

Posted on 2014-12-16
4
Medium Priority
?
194 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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

688 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