How to return set from a dimension based on sliced different dimension?

[tschuman13]

I'm working on a report where I need to count the number of new users who did not exist in the prior year.  I am having a hard time finding the right function to get my sets.  I don't have an actual date dimension since we are using Data Source -> Load Date for filters.  So each data source can have multiple load dates where values are different.

What I would like to do is use the except(set of all users based on year selected by user, set of all users based on prior year selected by user)

Is there a function that will give me a set from the user dimension based on a filtered the year dimension? 

[tjohnson3050]

If the category codes are the same for year between the two, you can use prompt macros to pass a category code value across dimensions:

#'[great_outdoors_sales_en].[Products].[Products].[Product line]->:[PC].[@MEMBER].['+prompt('PLine','token')+']'#

http://www-01.ibm.com/support/docview.wss?uid=swg21343356

[tschuman13]

I may need to do something like that to grab the prior year but I am still stuck trying to generate a set of users who existed by slicing my source->load date DIM.

Any idea on how to get this?

[tjohnson3050]

You would need to post more information about the structure of the dimensions in question.

[tschuman13]

Here are the dimensions in play:

Data Source -> Month -> Load Date (ie XXXX2011->July->7/11/2012)
Group -> Company -> UserID ( SmithGroup -> SmithComp - 12345)

User selects the 7/11/2012 load date from a prompt page.  I need a set of UserID's that were present in XXXX2011 data source and a set of UserID's that were present in XXXX2010 data source.  The 2 sets will then be used in an except() function to give me a list of new users.

I will probably need to dynamically create the MUN for the XXXX2012 data source but I have done this before and understand how its done.  What I can't figure out is how to get a filtered set of USERIDs for each year.

Any ideas?

[tjohnson3050]

It sounds like what you want to do is:

Filter the user level of the group dimension to get the users where the tuple of the current measure and the ancestor of the chosen date at the year (data source) level is greater than zero.

Try doing that using a function like:

filter([userid level],
tuple(ancestor(#prompt('load date','mun')#,[data source level]),currentMeasure)>0)