Last Non Empty
- From: jerstep@xxxxxxxxx
- Date: 16 Jun 2005 09:31:22 -0700
Hello.
My apologies for this post as I've searched through previous posts and
have found posts related to my problem but have been unable to get
exactly what I want.
I'm looking at personnel headcounts over over time dimension.
ie. Dimension Time with 3 levels. Year, Term, Month
I have a calculated member called Headcount, which is as follows:-
Iif([Time].Currentmember.Level.Name = "Year",
([Time].Currentmember.LastChild),
Iif([Time].Currentmember.Level.Name = "Term",
([Time].Currentmember.LastChild),[Measures].[Headcount]))
This displays the Headcount number on a monthly level and when
aggregating to a Term level, takes the figure from the last month.
Similarly, when aggregating to a Year level, the last Term figure is
displayed.
This is all fine and dandy, except when there are non values at a
monthly level i.e. we have not yet come to the end of the Term.
Can anyone help me modify the above MDX for my calculated member, to
display (when at a Term level) the last non-empty month figure ?
By way of an example:
Year Term Month Headcount
2004 11
2 11
Oct 10
Nov 11
Dec 12
Jan 13
Feb 12
Mar 11
2005 11
1 11
Apr 10
May 11
Jun
Jul
In the above example, the 2005 Term 1 total is 11 (i.e may's figure) as
there are not yet figures for June or July.
Any help much appreciated.
Regards,
J.
.
- Prev by Date: Re: MDX Query Between Dates
- Next by Date: Re: Using MSOLAP throught WEB, but with IIS and Analysis Services on different machines
- Previous by thread: Real Time OLAP with OWC does not work well?
- Next by thread: none aggregating measures in 2005 beta3
- Index(es):
Relevant Pages
|
