Print Page - topCount function and "Other" member

Title: topCount function and "Other" member - list report
Post by: emesemes on 04 Jul 2017 02:14:34 AM

Hi,

I am looking help regarding to topCount function and "other" member in list report. Mainly, I have Cognos BI 10.2.2. and TM1 10.3 as dimensional data source for Cognos. I want to prepare a list report as base for Watson Analytics, where I have top 5 members and all rest grouped as "other" member. I prepared something like this:

1) Top5: topCount ([Shops],5,tuple([2016-05],[Act],[Revenue]))
2a) Rest: except([shops], [top5])
2b) Rest Members: member(total(currentMeasure within set([Rest])),'Rest','Rest',[Cube].[Dim].[Shops])

Till now everything returns good results, but in the last step I want to marge 1 and 2b ? I want to have it on one column.

Column1|Column2
Top1|23121321
Top2|1233424
Top3|14324
Top4|5645
Top5|32
Other|43

I tried with something like this set(set([Top5],[RestMembers]) but doesn't work. Could you please help me with this?

Thx
emesemes

Title: Re: topCount function and "Other" member - list report
Post by: hespora on 04 Jul 2017 02:54:58 AM

http://cognospaul.com/2017/03/06/quickie-top-5-top-4-others/

Title: Re: topCount function and "Other" member - list report
Post by: emesemes on 04 Jul 2017 05:50:33 AM

Quote from: hespora on 04 Jul 2017 02:54:58 AM
http://cognospaul.com/2017/03/06/quickie-top-5-top-4-others/

Thx Hespora, this is great!! Looks good, I have good results. I move forward and I have a second issue. What if this "top+other" set is nested. E.g. in the first column I have "Levels([Organization Hierarchy],1)" in the next one "Levels([Organization Hierarchy],2)", and in the third this "top+other" - how to correspond to those first two columns? - now I have weird results in this set of columns.

Regards,
emesemes

Title: Re: topCount function and "Other" member - list report
Post by: hespora on 04 Jul 2017 08:41:24 AM

Hi emes,

unfortunately, I personally have no experience whatsoever with dimensional style reporting, so I couldn't possibly know how to solve that issue. I just realized that your original issue sounded exactly like that blog entry I read couple of weeks ago. ;)

Others here will surely be able to help you, though.

Title: Re: topCount function and "Other" member - list report
Post by: emesemes on 05 Jul 2017 01:45:33 AM

Quote from: hespora on 04 Jul 2017 08:41:24 AM
Hi emes,

unfortunately, I personally have no experience whatsoever with dimensional style reporting, so I couldn't possibly know how to solve that issue. I just realized that your original issue sounded exactly like that blog entry I read couple of weeks ago. ;)

Others here will surely be able to help you, though.

Hi hespora, thx for that. I hope that someone will know.
Regards,
emes

Title: Re: topCount function and "Other" member - list report
Post by: emesemes on 07 Jul 2017 02:10:28 PM

Anybody? Do you have any ideas? Is it possible at all?
emes

Title: Re: topCount function and "Other" member - list report
Post by: CognosPaul on 07 Jul 2017 03:14:54 PM

Can you describe the weird behavior you're seeing?

If I understand you have three levels in your hierarchy. Maybe region and country and city. So the first two levels are displayed, and then you see the top 5 + others cities for each country? If I'm wrong, can you post an example mockup of what you're hoping for?

Can you tell me what weird behavior you're seeing?

Title: Re: topCount function and "Other" member - list report
Post by: emesemes on 09 Jul 2017 03:33:03 PM

Title: Re: topCount function and "Other" member - list report
Post by: CognosPaul on 19 Jul 2017 05:40:36 PM

Can you post a copy of your report? I'd really need to see what you're doing here.

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: emesemes on 04 Jul 2017 02:14:34 AM