COGNOiSe.com - The IBM Cognos Community

Hi,

I have a prompt value and have a subset in a query item , i want to perform something by checking

if the selected member isv not in the subset then perform something.

subset - (Subset([Data Item1],[MonthIndex],3))  (2015JAN,2015FEB,2015MAR)
member - 2015JAN

so check if 2015JAN in (subset)

I tried caption , but on the subset side it throwed a error , i did caption(item(subset,1))

Any other simple way to check if a member exists in a set.

Thanks,

Thanks,

I used intersect to solve this, you can also use union I believe.

My example:
case: I wish to see if the Year "2017" exists in my memberset [Set of Years] = (2017,2018,2019,2020)

Dataitem1= intersect([Set of Years],[2017])    -- if member [2017] intersects with any of the members in the set it will generate [2017] i.e. one row.
Dataitem2 = if(Count(1 within set [Data Item1]) =1)
                       then ('Existing')
                       else('Non Existing')

If you wish to know how to do it with union you can check one of my old posts: https://www.cognoise.com/index.php?topic=35740.0 (https://www.cognoise.com/index.php?topic=35740.0)
You have to rewrite the code a little bit though to make it work with a memberset instead of an Level.

Best regards,
Oscar

Hi Oscarca,

Thansk for the suggestions and that intersect looks like it works. I am having a below use case changed like this.

User selects a month from a prompt and based on that selection i have to calculate my quarter sales until that month .

Ex: User selects 0ct 2020

I have the prompt value as Oct 2020 , I compute the quarter for the selected months using some functions and it returns Oct 2020,nov 2020 and dec 2020

Now i need to get only oct 2020

Now when user selects nov 2020 then the current quarter is (oct,nov dec 2020) and i want my result to be oct, nov 2020 .

oct 2020 selcted  -   
Quarter(oct,nov,dec 2020)
Required output(oct 2020)


nov 2020 selected
Quarter(oct,nov,dec 2020)
Required output (Oct ,nov 2020)

dec 2020 selected
Quarter(oct,nov,dec 2020)
Required output (Oct ,nov ,dec 2020)


What calcualtion can i use to get the required output result .

thanks,

to be more simple ,

I have a subset with a quarter

(oct2020, nov 2020 and dec 2020 )

I need to give me  a count of 1 if oct 2020 is selected
give me a count of 2 if nov 2020 is selected
give me a count of 3 if dec is selected .

Then i use this count on subset as subset(quarter,0 , CountValue)

I dont have quarter levels in my time dim, otherwise i would have done a periodstodate on quarter level , now i need to go a different route.

I was able to work by using
an if statement on caption of selected item with each of the three items in the subset to get the index.

Thanks,