Item, Index

[Francis aka khayman]

let us say for example I got a set

[Set A] = [Jan],[Feb],[Mar],[Apr]

item([Set A],3) returns [Mar]

is there a way to do a reverse, i want to know what is the index in the set of [Mar].

the end target is like this, I have two time dimensions and I need to pair a member of [T1] with member from [T2] two months back... 

this is how the columns in my crosstab should look like

      [T1Jan2014]                 [T1Feb2014]           [T1Mar2014] 
      [T2Nov2013]                [T2Dec2013]             [T2Jan2014]

[MDXpressor]

Hi khayman,
Is it a time dimension you're trying to work with?  Or is time just what you used for your example?

The reason I ask, is that 'Time' has it's own set of relative reference functions in Cognos (though you can use them for non-time dimensions).

So, if you wanted to use [Mar-2014] as your reference point in time, you can do a couple of things:
1: Call priorPeriod twice:  set ([Mar-2014], priorPeriod([Mar-2014), priorPeriod(priorPeriod([Mar-2014]) )
2:  periodToDate() may aslo be a good choice, depending on whether or not this really is a time dimension, and whether or not periodToDate can be specific enough.

If this is not ultimately for a date dimension, let's talk more.

[bdbits]

If this was a single time dimension, I think parallelPeriod() would work. But the complication is that it is in two different hierarchies, correct?

Completely untested, but maybe something like this would work.

cousin ( lag([T1].[Months].[date1],2), [T2].[Months])

The expression is probably going to get a lot more complicated if you use a T1 member that has less than 2 periods prior. I am honestly not sure what lag() will do with that. :-\

[Francis aka khayman]

thanks for your replies you guys. i have solved the issue.

this is the original issue:
http://www.cognoise.com/index.php/topic,25060.0.html

the post above is just one of the possible crazy solutions i thought of. but didn't work.

anyway here is the solution:
i was using currentMember on T1. at first i was confused why if i take the same months (T1 = T2) i get the correct value but if I take the two months prior (T1= T2 -2) using lag function on T1 I get a null value.

then after a couple of days (and sleepless nights) i got the idea what if i use the lag function on T2 instead of T1. it worked!!!

the explanation is T1 is returned by currentMember, which obviously just returns that one value, not including its siblings.

here is the correct expression:

lag(item(filter([Package].[T1].[Forecasting Month Dim].[Forecasting Month];roleValue('_memberCaption';[Package].[T1].[Forecasting Month Dim].[Forecasting Month]) = roleValue('_memberCaption';currentMember([Package].[T2].[Time Dim])));0);2)

before that i was using this expression (lag on T2):

item(filter([Package].[T1].[Forecasting Month Dim].[Forecasting Month];roleValue('_memberCaption';[Package].[T1].[Forecasting Month Dim].[Forecasting Month]) = roleValue('_memberCaption';lag(currentMember([Package].[T2].[Time Dim]);2));0)

i posted this solution in my blog as well for future reference:

http://cognosm.fm3online.com/dimensional/searching-a-dimension-using-value-from-another-dimension

[MDXpressor]

I didn't understand the original request, but I do understand the answer, it a good one.  I may have suggested nested queries with filters on the higher level, but this is a little more elegant, IMO.

BTW, do you need to use the 'rolevaule(_memberCaption'  function?  would 'caption()' not work in it's place?  using rolevalue may be showing your *cough* experience... ;-)

[Francis aka khayman]

:) originally i used caption with the incorrect expression. i thought it was the cause of the issue so i used roleValue instead but it still did not work. when i got the correct expression, i already have rolevalue so i decided to stick with it

I didn't understand the original request, but I do understand the answer, it a good one.  I may have suggested nested queries with filters on the higher level, but this is a little more elegant, IMO.

BTW, do you need to use the 'rolevaule(_memberCaption'  function?  would 'caption()' not work in it's place?  using rolevalue may be showing your *cough* experience... ;-)