topCount function and "Other" member - list report

[emesemes]

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

[hespora]

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

[emesemes]

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

[hespora]

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.

[emesemes]

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

[emesemes]

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

[CognosPaul]

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?

[emesemes]

Hi Paul,
Weird behavior. As you mentioned, let's assume that I have, 3-dimensional cube.

1 dim | Organization structure - 3 levels: Region, Country, Cities
2 dim | Shops
3 dim | Metrics: e.g. revenue, costs, etc.

I what to create a list report with columns as bellow:

Region (function levels (1)) | Country (function levels(2)) | Shops (top5) by Revenue | Revenue | costs


EMEA | Germany | Shop 1 | 800000 | 200000
EMEA | Germany | Shop 2 | 700000 | 300000
EMEA | Germany | Shop 3 | 610000 | 400000
EMEA | Germany | Shop 4 | 600000 | 400000
EMEA | Germany | Shop 5 | 400000 | 300000
EMEA | Germany | Other  | 9000001| 3000000
EMEA | France | Shop 1 | 800000 | 200000
EMEA | France | Shop 2 | 700000 | 300000
EMEA | France | Shop 3 | 610000 | 400000
EMEA | France | Shop 4 | 600000 | 400000
EMEA | France | Shop 5 | 400000 | 300000
EMEA | France | Other  | 9000001| 3000000
LATAM | Argentina | Shop 1 | 800000 | 200000
LATAM | Argentina | Shop 2 | 700000 | 300000
LATAM | Argentina | Shop 3 | 600000 | 200000
LATAM | Argentina | Shop 4 | 500000 | 300000
LATAM | Argentina | Shop 5 | 400000 | 300000
LATAM | Argentina | Other | 900000 | 300000


Now, when I add this "Region" and "Country" columns to the report I receive weird results, this "top 5" stop working, and I event don't know how this function receive feedback.

When I prepared a simpler version of this report w/o "Regin" and "Country" I have good results (top5 and other). I dont' know how to correspond to this additional "organization columns"

Regards,
emes

[CognosPaul]

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