Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Total for others after topcount

  1. #1
    Join Date
    Dec 2007
    Posts
    15

    Default Total for others after topcount

    Hi all,

    I have the following MDX to get top 100

    with set [Top Clients] as 'TopCount([Client].Members, 100.0, [Measures].[Revenue])'
    select nonemptyCrossjoin({[Measures].[Revenue]}, {[Year].Children}) ON COLUMNS,
    nonemptyCrossjoin([Top Clients], {[Services]}) ON ROWS
    from [PF]


    and I would like to add a line with total for remaining Clients. How can I do this? I tried using calculatedMember but with no luck.

    Regards,

    Mircha

  2. #2
    Join Date
    May 2007
    Posts
    23

    Default Remaining total

    Mircha,

    I recommend the following MDX snippet that worked fine in my case:

    with set [Top Clients] as 'TopCount([Client].Members, 100.0, [Measures].[Revenue])'

    member [Client].[Remaining total] as '[Client].[All] - Sum([Top Clients])'

    It requires to have an all member on your dimension.


  3. #3
    Join Date
    Dec 2007
    Posts
    15

    Default Select statement

    Many thanks olapdesigner,

    This is what I was looking for. And how to include it into select statement so it has crossjoin with second dimension?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.