Question
· Jan 5, 2021

How to define a set range using member functions?

Is it supported to be able to use member functions to define a set, in particular a set defined with a member range in the form:

set = member1:member2? 

The docs only show hardcoded values being used for member1 and member2 and I can get it to work if I use hardcoded values.  However if I try to use member functions it throws an error:

"ERROR #5001: Both parts of a range must be members"

While the below does not give me the correct answers without using the %All function, it does work.  It's not workable for a typical application that needs dynamic dates (and/or using the dates on the axis) unless I do some kind of dynamic MDX.  I've experimented with other set functions like COUNT, all have the same issue.  Is it possible to do?

 

I know that %TIMERANGE can work for some things but that returns only a member that contains a total.  It cannot be used to operate on the individual members. 

If for each date, I want a count of days in the past 30 days that have [TotalTrx]>0, I would normally handle this with something like:

sum(startmember-29:startmember,iif([TotalTrx]>0,1,0)). 

I can't figure out how something like that could be implemented if set functions can't take a set argument that is a member range based off of member functions (like CURRENTMEMBER).

Discussion (2)0
Log in or sign up to continue