Help with MDX using Stat Account in a specific time member

#1

I am currently using a number of statistical accounts that contain adjustment factors used in calculated accounts. These factors are consistent throughout the year and I would like to only store these factors in December of each year to reduce the system load. What I have not been able to find is how I can point the calculated account to always look at the last month of the year. I have had partial success using [Time].[Default].LastSibling but that only works to pull from the last member in the quarter, but not the year.

Any help would be greatly appreciated.

#2

Hi,

I have default Hierarchy in my Time Dimension
Default–>Year
Default–>Quarter
Default–>Month

If use the follow MDX, it works as intended, to provide last 4 periods by month.

Select LastPeriods(4,[Time].[Default].[Month].&[2022M03]) on 0 from [XXX Cube]

Where 2022M03 is my Month Key.

I hope this works for you.

Regards,

Ali Arsalan

1 Like
#3

Thank you for the suggestion. I am not sure that this will fit in my situation as I am looking to use this in a calculated account to dynamically reference the last month of the same year as the current member. I tried using LastPeriods(1,[Time].[Default].[Month].CurrentMember) but end up with an error. That could also be due to my misunderstanding of the function.

To add a little more context and an update, I was able to get the desired result by pulling the value at the Year level using:
Ancestor([Time].Default.Currentmember, [Time].Default.[Year])

This works because the Time Conversion on the account with the adjustment factor is “Average (excluding empty)” and the calculation this is going into is a Leaf Formula. By only loading the adjustment factor into December the values are the same. I am not sure if this is the most efficient or ideal method pulling from the Year level instead of the Month, but it gets the correct result.

1 Like
#4

Hi,

LastPeriods, results in previous period, if you use first period of next year as filter than it will always bring Dec of last year, that way you can be exact regarding the Period.

However, if you want YTD value than your formula looks better as it will always bring Year value of whatever the CurrentMember may be.

2 Likes
#5

Sorry, I dont think i can help you with this.

#6

Besides what the previous people have said earlier, I don’t know of any other ways.

#7

@justin.buttles, this was going to be my suggested approach. Glad you found it! Though I was going to suggest using “last excluding empty” or even “last including empty” if you’re sure it will always be December and want the calculation to go to 0 if you haven’t provided a value yet.

(I like your Average approach, though, because that resolves the missing value problem nicely and intuitively…I’ll probably make more use of that myself now that you’ve brought it to my attention.)

2 Likes
#8

Following the different approaches of using stat accounts.

#9

We use a number of ways to look up different months during the year - what about setting up a new ‘named set time’ that you can use in your calculations but you can change the month in that time to look at December (or whichever month you want)?

1 Like
#10

Thank you for proving this insight!

#11

Very insightful read and will try to use something similar within our process. Thanks for sharing!

#12

Thank you for proving this insight!

#13

Great to see others with insight.

#14

Hey @justin.buttles

Howdy buddy? :slight_smile:

One option I’ve used is
Create an alternate hierarchy in the time dimension that is a flat structure i.e. Year >> Months
Let’s say this is called “YearMonth”
image

The below MDX will then point to the last member of the YearMonth hierarchy (aforementioned above)
([Time Perspective].[Default].&[BASE],[Time].[YearMonth].CurrentMember.LastSibling)

Will this help?

Cheers,
Navin

3 Likes
#15

Great to see others with insight.

#16

Hey Navin!
@navin.sadarangani

I knew I could count on you coming up with a solution! I hadn’t thought about using the alternate hierarchy to remove the quarters, but that gives a great solution and allows the use of LastSibling for the full year.

This will help clean up thousands of those excess conversion factors we use in some of our cubes, and will be helpful in other areas where the time conversion may be different than Average excluding blanks.

1 Like
#17

Most welcome, Justin. @justin.buttles Glad to be of help :slight_smile:

#18

Appreciate the questions and insights. This opens up ideas for so many possibilities. Thank you

#19

Thanks for the information. Interesting for consideration.

#20

I tend to use alternate hierarchies for situations like this.