Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL 2008 SSAS MDX formula

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
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…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

877 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